using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace WP.Controls
{

    public class ComFunc
    {
        public ComFunc()
		{
			//
			// TODO: Add constructor logic here
			//
		}

        #region Data Process declare variable
        private SqlConnection m_scnCon = null;
        private SqlDataAdapter m_sdaAdapter = null;
        private SqlCommand m_scmCom = null;

        #endregion

        #region Define enum
        public enum SqlOption
        {
            SqlPartTableName = 0,
            SqlPartMain = 1,
            SqlPartWhere = 2,
            SqlPartOrderBy = 3,
            SqlPartInner = 4,
            SqlPartAll = 5
        }

        public enum GridDtlStyle
        {
            Calendar,
            Image,
            List,
            Tally,
            TextBox,
            TextLookUp
        }

        public enum DataType
        {
            String,
            DateTime,
            Integer,
            Numeric,
            Bool,
            Byte
        }
        public enum PageOrient
        {
            Top, Prev, Next, Bottom, PageUp, PageDown, None
        }
        public struct PageInfo
        {
            public PageOrient nOrient;
            public long lBeginRecID;
            public string strPrimaryKeyCon;
            public string strTableSuitCon;
            public long lBeginRecID_Last;
            public string strTableSuitCon_Last;
            public string strFindField;
            public string strFindValue;
            public int iRowsPerPage;
            ////public string[][] arrKeyField;
            ////public int iParameter;
        }
        #endregion

        #region Get sqlpart according to intSqlPart
        /// <summary>
        /// Get sqlpart according to intSqlPart
        /// </summary>
        /// <param name="strSQl">sql statement</param>
        /// <param name="sqlOption">sql option</param>
        /// SqlOption.SqlPartTablename  0
        /// SqlOption.SqlPartMain       1
        /// SqlOption.SqlPartWhere      2
        /// SqlOption.SqlPartOrderBy    3
        /// SqlOption.SqlPartInner      4
        /// SqlOption.SqlPartAll        5
        /// <returns>sqlpart according to intSqlPart</returns>
        public static string GetSqlParts(string strSQl, SqlOption sqlOption)
        {
            string strResult = String.Empty;

            int iStart = 0;
            int iEnd = 0;
            int iFieldCount = 0;
            string strMySQl = String.Empty;
            string strChar = String.Empty;

            int iSelectStart = 0;
            int iTableNameStart = 0;
            int iWhereStart = 0;
            int iOrderbyStart = 0;
            int iInnerStart = 0;
            int iLeftJoinStart = 0;
            int i = 0;

            string strTableName = String.Empty;
            string strField = String.Empty;
            string strMain = String.Empty;
            string strWhere = String.Empty;
            string strOrderby = String.Empty;
            string strInner = String.Empty;
            string strLeftJoin = String.Empty;
            //strSQl = strSQl.ToUpper();

            if (strSQl.Length <= 32766 && strSQl != null && strSQl.Trim().Length != 0)
            {
                strMySQl = strSQl.Trim();
                strMySQl = strMySQl.Replace("\n", " ").Replace("\t", " ").Replace("\r", " ");

                //calculate key word start position
                iSelectStart = GetSqlKeywordPos(strMySQl, "Select".ToUpper());
                iTableNameStart = GetSqlKeywordPos(strMySQl, "From".ToUpper());
                iWhereStart = GetSqlKeywordPos(strMySQl, "Where".ToUpper());
                iOrderbyStart = GetSqlKeywordPos(strMySQl, "Order by".ToUpper());
                iInnerStart = GetSqlKeywordPos(strMySQl, "Inner join".ToUpper());
                iLeftJoinStart = GetSqlKeywordPos(strMySQl, "left join".ToUpper());


                //Get TableName
                if (sqlOption == SqlOption.SqlPartTableName)
                {
                    #region ***Get Table Name
                    if (iTableNameStart > -1)
                    {
                        iStart = iTableNameStart;
                        //
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 4)//Len("from")=4
                        {
                            strResult = strMySQl.Substring(iStart + 5, iEnd - iStart - 4 - 1);
                            strResult = strResult.TrimStart();
                            i = strResult.IndexOf(' ');
                            if (i > 1) strResult = strResult.Substring(0, i);
                        }
                        else
                        {
                            if (iSelectStart == 0 && iTableNameStart == 0 && iWhereStart == 0
                                && iOrderbyStart == 0 && iInnerStart == 0 && iLeftJoinStart == 0)
                            {
                                strResult = strMySQl;
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartInner)
                {
                    #region ***Get Inner Sql
                    if (iInnerStart > -1)
                    {
                        iStart = iInnerStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 10)
                        {
                            strResult = strMySQl.Substring(iStart + 11, iEnd - iStart - 10 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " inner join ");
                            }
                        }
                    }

                    if (iLeftJoinStart > -1)
                    {
                        iStart = iLeftJoinStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 9)
                        {
                            strResult = strMySQl.Substring(iStart + 10, iEnd - iStart - 9 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " left join ");
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartWhere)
                {
                    #region ***Get Where Sql
                    if (iWhereStart > -1)
                    {
                        iStart = iWhereStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 5)
                        {
                            strResult = strMySQl.Substring(iStart + 6, iEnd - iStart - 5 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " where ");
                            }
                        }
                    }
                    #endregion
                }
                else if (sqlOption == SqlOption.SqlPartOrderBy)
                {
                    #region ***Get Orderby Sql
                    if (iOrderbyStart > -1)
                    {
                        iStart = iOrderbyStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 9)
                        {
                            strResult = strMySQl.Substring(iStart + 9, iEnd - iStart - 8 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " ORDER BY ");
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartMain)
                {
                    #region ***Get Main Sql
                    strTableName = GetSqlParts(strMySQl, SqlOption.SqlPartTableName);
                    if (strTableName.Length != 0)
                    {
                        if (iSelectStart > -1)
                        {
                            iStart = iSelectStart;
                            //GoSub GetEndPos
                            iEnd = strMySQl.Length;
                            if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                            if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                            if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                            if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                            if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                            //
                            if (iEnd - iStart > 6)
                            {
                                strField = strMySQl.Substring(iStart + 7, iEnd - iStart - 6 - 1).Trim();
                                //RemakeFields: rebuild every field
                                if (strField.StartsWith("*") == false && strField.StartsWith("all") == false)
                                {
                                    strChar = String.Empty;
                                    strField += ",";
                                    i = GetSqlKeywordPos(strField, ",");

                                    while (i > -1)
                                    {
                                        if (i == 0)
                                        {
                                            strField = strField.Remove(0, 1);
                                        }
                                        else
                                        {
                                            if (strField.Substring(0, i).Trim() != String.Empty)
                                            {
                                                if (GetSqlKeywordPos(strField.Substring(0, i).Trim(), "as") > 0)
                                                {
                                                    strChar += strField.Substring(0, i).Trim() + ",";
                                                }
                                                else if ((GetSqlKeywordPos(strField.Substring(0, i).Trim(), "distinct") > 0))
                                                {
                                                    strChar += strField.Substring(0, i).Trim() + ",";
                                                }
                                                else if (strField.Substring(0, i).Trim().StartsWith("[") == true && strField.Substring(0, i).Trim().EndsWith("]") == true)
                                                {
                                                    strChar += strField.Substring(0, i).Trim() + ",";
                                                }
                                                //else if(strField.IndexOf(".") > -1)
                                                //{
                                                //    strChar=strField.Replace(".","")+",";
                                                //}
                                                else if ((strField.Substring(0, i).Trim().IndexOf(".") > -1) && (strField.Substring(0, i).IndexOf(".") < strField.Substring(0, i).Trim().Length))
                                                {
                                                    //                                                    strChar += Left(Trim(Left(strField, i - 1)), InStr(1, Trim(Left(strField, i - 1)), "."));
                                                    //                                                    strChar += Right(Trim(Left(strField, i - 1)), Len(Trim(Left(strField, i - 1))) - InStr(1, Trim(Left(strField, i - 1)), ".")) + ",";

                                                    strChar += strField.Substring(0, i).Trim().Substring(0, strField.Substring(0, i).Trim().IndexOf("."));
                                                    strChar += strField.Substring(0, i).Trim().Substring(strField.Substring(0, i).Trim().Length - (strField.Substring(0, i).Trim().Length - strField.Substring(0, i).Trim().IndexOf(".")), strField.Substring(0, i).Trim().Length - strField.Substring(0, i).Trim().IndexOf(".")) + ",";
                                                }
                                                else
                                                {
                                                    strChar += "[" + strField.Substring(0, i).Trim() + "],";
                                                }
                                                iFieldCount++;

                                            }
                                            strField = strField.Substring(i + 1).Trim();

                                        }
                                        i = GetSqlKeywordPos(strField, ",");

                                    }
                                    if (strChar == String.Empty)
                                    {
                                        strField = String.Empty;
                                    }
                                    else
                                    {
                                        strField = strChar;
                                        while (strField.EndsWith("\t") || strField.EndsWith("\n") || strField.EndsWith(","))
                                        {
                                            strField = strField.Remove(strField.Length - 1, 1);
                                        }
                                    }

                                }
                                //

                                if (strField.Length != 0)
                                {
                                    strMain = "select " + strField + " from " + strTableName;
                                    strResult = strMain;
                                }
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartAll)
                {
                    #region ***Get All Sql
                    strMain = GetSqlParts(strMySQl, SqlOption.SqlPartMain);
                    if (strMain.Length != 0)
                    {
                        //
                        strInner = GetSqlParts(strMySQl, SqlOption.SqlPartInner);
                        strWhere = GetSqlParts(strMySQl, SqlOption.SqlPartWhere);
                        strOrderby = GetSqlParts(strMySQl, SqlOption.SqlPartOrderBy);
                        strResult = strMain + String.Format(" {0} {1} {2} ", strInner, strWhere, strOrderby);
                    }
                    #endregion ***
                }

            }

            return strResult;
        }

        public static string GetSqlParts(string strSQl, SqlOption sqlOption, ref string[] fieldArray)
        {
            string strResult = String.Empty;

            int iStart = 0;
            int iEnd = 0;
            int iFieldCount = 0;
            string strMySQl = String.Empty;
            string strChar = String.Empty;
            string strFieldArray = String.Empty;

            int iSelectStart = 0;
            int iTableNameStart = 0;
            int iWhereStart = 0;
            int iOrderbyStart = 0;
            int iInnerStart = 0;
            int iLeftJoinStart = 0;
            int i = 0;

            string strTableName = String.Empty;
            string strField = String.Empty;
            string strMain = String.Empty;
            string strWhere = String.Empty;
            string strOrderby = String.Empty;
            string strInner = String.Empty;
            string strLeftJoin = String.Empty;
            //strSQl = strSQl.ToUpper();

            if (strSQl.Length <= 32766 && strSQl != null && strSQl.Trim().Length != 0)
            {
                strMySQl = strSQl.Trim();
                strMySQl = strMySQl.Replace("\n", " ").Replace("\t", " ").Replace("\r", " ");

                //calculate key word start position
                iSelectStart = GetSqlKeywordPos(strMySQl, "Select".ToUpper());
                iTableNameStart = GetSqlKeywordPos(strMySQl, "From".ToUpper());
                iWhereStart = GetSqlKeywordPos(strMySQl, "Where".ToUpper());
                iOrderbyStart = GetSqlKeywordPos(strMySQl, "Order by".ToUpper());
                iInnerStart = GetSqlKeywordPos(strMySQl, "Inner join".ToUpper());
                iLeftJoinStart = GetSqlKeywordPos(strMySQl, "left join".ToUpper());


                //Get TableName
                if (sqlOption == SqlOption.SqlPartTableName)
                {
                    #region ***Get Table Name
                    if (iTableNameStart > -1)
                    {
                        iStart = iTableNameStart;
                        //
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 4)//Len("from")=4
                        {
                            strResult = strMySQl.Substring(iStart + 5, iEnd - iStart - 4 - 1);
                            strResult = strResult.TrimStart();
                            i = strResult.IndexOf(' ');
                            if (i > 1) strResult = strResult.Substring(0, i);
                        }
                        else
                        {
                            if (iSelectStart == 0 && iTableNameStart == 0 && iWhereStart == 0
                                && iOrderbyStart == 0 && iInnerStart == 0 && iLeftJoinStart == 0)
                            {
                                strResult = strMySQl;
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartInner)
                {
                    #region ***Get Inner Sql
                    if (iInnerStart > -1)
                    {
                        iStart = iInnerStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 10)
                        {
                            strResult = strMySQl.Substring(iStart + 11, iEnd - iStart - 10 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " inner join ");
                            }
                        }
                    }

                    if (iLeftJoinStart > -1)
                    {
                        iStart = iLeftJoinStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 9)
                        {
                            strResult = strMySQl.Substring(iStart + 10, iEnd - iStart - 9 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " left join ");
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartWhere)
                {
                    #region ***Get Where Sql
                    if (iWhereStart > -1)
                    {
                        iStart = iWhereStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 5)
                        {
                            strResult = strMySQl.Substring(iStart + 6, iEnd - iStart - 5 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " where ");
                            }
                        }
                    }
                    #endregion
                }
                else if (sqlOption == SqlOption.SqlPartOrderBy)
                {
                    #region ***Get Orderby Sql
                    if (iOrderbyStart > -1)
                    {
                        iStart = iOrderbyStart;
                        //GoSub GetEndPos
                        iEnd = strMySQl.Length;
                        if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                        if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                        if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                        if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                        if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                        //
                        if (iEnd - iStart > 9)
                        {
                            strResult = strMySQl.Substring(iStart + 9, iEnd - iStart - 8 - 1);
                            if (strResult.Length != 0)
                            {
                                strResult = strResult.Insert(0, " ORDER BY ");
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartMain)
                {
                    #region ***Get Main Sql
                    strTableName = GetSqlParts(strMySQl, SqlOption.SqlPartTableName);
                    if (strTableName.Length != 0)
                    {
                        if (iSelectStart > -1)
                        {
                            iStart = iSelectStart;
                            //GoSub GetEndPos
                            iEnd = strMySQl.Length;
                            if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
                            if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
                            if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
                            if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
                            if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
                            //
                            if (iEnd - iStart > 6)
                            {
                                strField = strMySQl.Substring(iStart + 7, iEnd - iStart - 6 - 1).Trim();
                                //RemakeFields: rebuild every field
                                if (strField.StartsWith("*") == false && strField.StartsWith("all") == false)
                                {
                                    strChar = String.Empty;
                                    strField += ",";
                                    i = GetSqlKeywordPos(strField, ",");

                                    while (i > -1)
                                    {
                                        if (i == 0)
                                        {
                                            strField = strField.Remove(0, 1);
                                        }
                                        else
                                        {
                                            if (strField.Substring(0, i).Trim() != String.Empty)
                                            {
                                                if (GetSqlKeywordPos(strField.Substring(0, i).Trim(), "as") > 0)
                                                {
                                                    strChar += strField.Substring(0, i).Trim() + ",";
                                                    strFieldArray += strField.Substring(0, i).Trim() + "?";
                                                }
                                                else if ((GetSqlKeywordPos(strField.Substring(0, i).Trim(), "distinct") > 0))
                                                {
                                                    strChar += strField.Substring(0, i).Trim() + ",";
                                                    strFieldArray += strField.Substring(0, i).Trim() + "?";
                                                }
                                                else if (strField.Substring(0, i).Trim().StartsWith("[") == true && strField.Substring(0, i).Trim().EndsWith("]") == true)
                                                {
                                                    strChar += strField.Substring(0, i).Trim() + ",";
                                                    strFieldArray += strField.Substring(0, i).Trim() + "?";
                                                }
                                                //else if(strField.IndexOf(".") > -1)
                                                //{
                                                //    strChar=strField.Replace(".","")+",";
                                                //}
                                                else if ((strField.Substring(0, i).Trim().IndexOf(".") > -1) && (strField.Substring(0, i).IndexOf(".") < strField.Substring(0, i).Trim().Length))
                                                {
                                                    //                                                    strChar += Left(Trim(Left(strField, i - 1)), InStr(1, Trim(Left(strField, i - 1)), "."));
                                                    //                                                    strChar += Right(Trim(Left(strField, i - 1)), Len(Trim(Left(strField, i - 1))) - InStr(1, Trim(Left(strField, i - 1)), ".")) + ",";

                                                    strChar += strField.Substring(0, i).Trim().Substring(0, strField.Substring(0, i).Trim().IndexOf("."));
                                                    strFieldArray += strField.Substring(0, i).Trim().Substring(0, strField.Substring(0, i).Trim().IndexOf("."));
                                                    strChar += strField.Substring(0, i).Trim().Substring(strField.Substring(0, i).Trim().Length - (strField.Substring(0, i).Trim().Length - strField.Substring(0, i).Trim().IndexOf(".")), strField.Substring(0, i).Trim().Length - strField.Substring(0, i).Trim().IndexOf(".")) + ",";
                                                    strFieldArray += strField.Substring(0, i).Trim().Substring(strField.Substring(0, i).Trim().Length - (strField.Substring(0, i).Trim().Length - strField.Substring(0, i).Trim().IndexOf(".")), strField.Substring(0, i).Trim().Length - strField.Substring(0, i).Trim().IndexOf(".")) + "?";
                                                }
                                                else
                                                {
                                                    strChar += "[" + strField.Substring(0, i).Trim() + "],";
                                                    strFieldArray += "[" + strField.Substring(0, i).Trim() + "]?";
                                                }
                                                iFieldCount++;

                                            }
                                            strField = strField.Substring(i + 1).Trim();

                                        }
                                        i = GetSqlKeywordPos(strField, ",");

                                    }
                                    if (strChar == String.Empty)
                                    {
                                        strField = String.Empty;
                                    }
                                    else
                                    {
                                        strField = strChar;
                                        while (strField.EndsWith("\t") || strField.EndsWith("\n") || strField.EndsWith(","))
                                        {
                                            strField = strField.Remove(strField.Length - 1, 1);
                                        }
                                    }

                                }
                                //

                                if (strField.Length != 0)
                                {
                                    strMain = "select " + strField + " from " + strTableName;
                                    strResult = strMain;
                                }

                                if (strFieldArray.Length != 0)
                                {
                                    strFieldArray = strFieldArray.TrimEnd('?');
                                    fieldArray = strFieldArray.Split('?');
                                }
                            }
                        }
                    }
                    #endregion ***
                }
                else if (sqlOption == SqlOption.SqlPartAll)
                {
                    #region ***Get All Sql
                    strMain = GetSqlParts(strMySQl, SqlOption.SqlPartMain);
                    if (strMain.Length != 0)
                    {
                        //
                        strInner = GetSqlParts(strMySQl, SqlOption.SqlPartInner);
                        strWhere = GetSqlParts(strMySQl, SqlOption.SqlPartWhere);
                        strOrderby = GetSqlParts(strMySQl, SqlOption.SqlPartOrderBy);
                        strResult = strMain + String.Format(" {0} {1} {2} ", strInner, strWhere, strOrderby);
                    }
                    #endregion ***
                }

            }

            return strResult;
        }

        ////public static string GetSqlParts(string strSQl, SqlOption sqlOption)
        ////{
        ////    string strResult = String.Empty; 

        ////    int iStart = 0;
        ////    int iEnd = 0;
        ////    int iFieldCount = 0;
        ////    string strMySQl = String.Empty;
        ////    string strChar = String.Empty;

        ////    int iSelectStart = 0;
        ////    int iTableNameStart = 0;
        ////    int iWhereStart = 0;
        ////    int iOrderbyStart = 0;
        ////    int iInnerStart = 0;
        ////    int iLeftJoinStart = 0;
        ////    int i = 0;

        ////    string strTableName = String.Empty;
        ////    string strField = String.Empty;
        ////    string strMain = String.Empty;
        ////    string strWhere = String.Empty;
        ////    string strOrderby = String.Empty;
        ////    string strInner = String.Empty;
        ////    string strLeftJoin = String.Empty;
        ////    strSQl = strSQl.ToUpper();

        ////    if (strSQl.Length <= 32766 && strSQl != null && strSQl.Trim().Length != 0)
        ////    {
        ////        strMySQl = strSQl.Trim();
        ////        strMySQl = strMySQl.Replace("\n", " ").Replace("\t", " ").Replace("\r", " ");

        ////        //calculate key word start position
        ////        iSelectStart = GetSqlKeywordPos(strMySQl, "Select".ToUpper());
        ////        iTableNameStart = GetSqlKeywordPos(strMySQl, "From".ToUpper());
        ////        iWhereStart = GetSqlKeywordPos(strMySQl, "Where".ToUpper());
        ////        iOrderbyStart = GetSqlKeywordPos(strMySQl, "Order by".ToUpper());
        ////        iInnerStart = GetSqlKeywordPos(strMySQl, "Inner join".ToUpper());
        ////        iLeftJoinStart = GetSqlKeywordPos(strMySQl, "left join".ToUpper());


        ////        //Get TableName
        ////        if (sqlOption == SqlOption.SqlPartTableName)
        ////        {
        ////            if (iTableNameStart > -1)
        ////            {
        ////                iStart = iTableNameStart;
        ////                //
        ////                iEnd = strMySQl.Length;
        ////                if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
        ////                if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
        ////                if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
        ////                if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
        ////                if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
        ////                //
        ////                if (iEnd - iStart > 4)//Len("from")=4
        ////                {
        ////                    strResult = strMySQl.Substring(iStart + 5, iEnd - iStart - 4 - 1);
        ////                    strResult = strResult.TrimStart();
        ////                    strResult.Insert(0, " ");
        ////                }
        ////                else
        ////                {
        ////                    if (iSelectStart == 0 && iTableNameStart == 0 && iWhereStart == 0
        ////                        && iOrderbyStart == 0 && iInnerStart == 0 && iLeftJoinStart == 0)
        ////                    {
        ////                        strResult = strMySQl;
        ////                    }
        ////                }
        ////            }
        ////        }
        ////        else if (sqlOption == SqlOption.SqlPartInner)
        ////        {
        ////            if (iInnerStart > -1)
        ////            {
        ////                iStart = iInnerStart;
        ////                //GoSub GetEndPos
        ////                iEnd = strMySQl.Length;
        ////                if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
        ////                if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
        ////                if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
        ////                if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
        ////                if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
        ////                //
        ////                if (iEnd - iStart > 10)
        ////                {
        ////                    strResult = strMySQl.Substring(iStart + 11, iEnd - iStart - 10);
        ////                    if (strResult.Length != 0)
        ////                    {
        ////                        strResult = strResult.Insert(0, " inner join ");
        ////                    }
        ////                }

        ////                if (iLeftJoinStart > -1)
        ////                {
        ////                    iStart = iLeftJoinStart;
        ////                    //GoSub GetEndPos
        ////                    iEnd = strMySQl.Length;
        ////                    if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
        ////                    if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
        ////                    if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
        ////                    if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
        ////                    if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
        ////                    //
        ////                    if (iEnd - iStart > 9)
        ////                    {
        ////                        strResult = strMySQl.Substring(iStart + 10, iEnd - iStart - 9);
        ////                        if (strResult.Length != 0)
        ////                        {
        ////                            strResult = strResult.Insert(0, " left join ");
        ////                        }
        ////                    }
        ////                }
        ////            }
        ////        }
        ////        else if (sqlOption == SqlOption.SqlPartWhere)
        ////        {
        ////            if (iWhereStart > -1)
        ////            {
        ////                iStart = iWhereStart;
        ////                //GoSub GetEndPos
        ////                iEnd = strMySQl.Length;
        ////                if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
        ////                if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
        ////                if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
        ////                if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
        ////                if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
        ////                //
        ////                if (iEnd - iStart > 5)
        ////                {
        ////                    strResult = strMySQl.Substring(iStart + 6, iEnd - iStart - 5);
        ////                    if (strResult.Length != 0)
        ////                    {
        ////                        strResult = strResult.Insert(0, " where ");
        ////                    }
        ////                }
        ////            }
        ////        }
        ////        else if (sqlOption == SqlOption.SqlPartOrderBy)
        ////        {
        ////            if (iOrderbyStart > -1)
        ////            {
        ////                iStart = iOrderbyStart;
        ////                //GoSub GetEndPos
        ////                iEnd = strMySQl.Length;
        ////                if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
        ////                if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
        ////                if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
        ////                if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
        ////                if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
        ////                //
        ////                if (iEnd - iStart > 9)
        ////                {
        ////                    strResult = strMySQl.Substring(iStart + 9, iEnd - iStart - 9);
        ////                    if (strResult.Length != 0)
        ////                    {
        ////                        strResult = strResult.Insert(0, " ORDER BY ");
        ////                    }
        ////                }
        ////            }
        ////        }
        ////        else if (sqlOption == SqlOption.SqlPartMain)
        ////        {
        ////            strTableName = GetSqlParts(strMySQl, SqlOption.SqlPartTableName);
        ////            if (strTableName.Length != 0)
        ////            {
        ////                if (iSelectStart > -1)
        ////                {
        ////                    iStart = iSelectStart;
        ////                    //GoSub GetEndPos
        ////                    iEnd = strMySQl.Length;
        ////                    if (iStart < iSelectStart && iEnd > iSelectStart) { iEnd = iSelectStart - 1; }
        ////                    if (iStart < iTableNameStart && iEnd > iTableNameStart) { iEnd = iTableNameStart - 1; }
        ////                    if (iStart < iInnerStart && iEnd > iInnerStart) { iEnd = iInnerStart - 1; }
        ////                    if (iStart < iWhereStart && iEnd > iWhereStart) { iEnd = iWhereStart - 1; }
        ////                    if (iStart < iOrderbyStart && iEnd > iOrderbyStart) { iEnd = iOrderbyStart - 1; }
        ////                    //
        ////                    if (iEnd - iStart > 6)
        ////                    {
        ////                        strField = strMySQl.Substring(iStart + 7, iEnd - iStart - 6).Trim();
        ////                        // GoSub RemakeFields
        ////                        if (strField.StartsWith("*") == false && strField.StartsWith("all") == false)
        ////                        {
        ////                            strChar = String.Empty;
        ////                            strField += ",";
        ////                            i = GetSqlKeywordPos(strField, ",");

        ////                            while (i > -1)
        ////                            {
        ////                                if (i == 0)
        ////                                {
        ////                                    strField = strField.Remove(0, 1);
        ////                                }
        ////                                else
        ////                                {
        ////                                    if (strField.Substring(0, i).Trim() != String.Empty)
        ////                                    {
        ////                                        if (GetSqlKeywordPos(strField.Substring(0, i).Trim(), "as") > 0)
        ////                                        {
        ////                                            strChar += strField.Substring(0, i).Trim() + ",";
        ////                                        }
        ////                                        else if ((GetSqlKeywordPos(strField.Substring(0, i).Trim(), "distinct") > 0))
        ////                                        {
        ////                                            strChar += strField.Substring(0, i).Trim() + ",";
        ////                                        }
        ////                                        else if (strField.Substring(0, i).Trim().StartsWith("[") == true && strField.Substring(0, i).Trim().EndsWith("]") == true)
        ////                                        {
        ////                                            strChar += strField.Substring(0, i).Trim() + ",";
        ////                                        }
        ////                                        else if (strField.IndexOf(".") > -1)
        ////                                        {
        ////                                            strChar = strField.Replace(".", "") + ",";
        ////                                        }
        ////                                        else
        ////                                        {
        ////                                            strChar += "[" + strField.Substring(0, i).Trim() + "],";
        ////                                        }
        ////                                        iFieldCount++;

        ////                                    }
        ////                                    strField = strField.Substring(i + 1);

        ////                                }
        ////                                i = GetSqlKeywordPos(strField, ",");

        ////                            }
        ////                            if (strChar == String.Empty)
        ////                            {
        ////                                strField = String.Empty;
        ////                            }
        ////                            else
        ////                            {
        ////                                strField = strChar;
        ////                                while (strField.EndsWith("\t") || strField.EndsWith("\n") || strField.EndsWith(","))
        ////                                {
        ////                                    strField = strField.Remove(strField.Length - 1, 1);
        ////                                }
        ////                            }

        ////                        }
        ////                        //

        ////                        if (strField.Length != 0)
        ////                        {
        ////                            strMain = "select " + strField + " from " + strTableName;
        ////                            strResult = strMain;
        ////                        }
        ////                    }
        ////                }
        ////            }
        ////        }
        ////        else if (sqlOption == SqlOption.SqlPartAll)
        ////        {
        ////            strMain = GetSqlParts(strMySQl, SqlOption.SqlPartMain);
        ////            if (strMain.Length != 0)
        ////            {
        ////                //
        ////                strInner = GetSqlParts(strMySQl, SqlOption.SqlPartInner);
        ////                strWhere = GetSqlParts(strMySQl, SqlOption.SqlPartWhere);
        ////                strOrderby = GetSqlParts(strMySQl, SqlOption.SqlPartOrderBy);
        ////                strResult = strMain + String.Format(" {0} {1} {2} ", strInner, strWhere, strOrderby);
        ////            }
        ////        }

        ////    }

        ////    return strResult;
        ////}

        #endregion

        #region public int GetSqlKeywordPos() +1

        public static int GetSqlKeywordPos(string strSQl, string strKeyword, int intStart)
        {
            int returnValue;
            //*********************************************************************
            //Function:Get a key word absolute position from a SQL string
            //         this key word does not in "( )" or "[ ]"
            //         this key word maybe a separator ","
            //Return:If find return start position(is >=1), not find return 0
            //Author:Wang Gang 2000/11/16
            //*********************************************************************
            int i;
            int intPos = -1; //current pos
            int intPrPos = 0; //previous pos
            int intCLCount = -1; // "(" count
            int intCRCount = -1; // ")" count
            int intSLCount = -1; // "[" count
            int intSRCount = -1; // "]" count

            //int iPos = -1; //current pos
            //int iPrPos = 0; //previous pos
            //int iCLCount = -1;// "(" count
            //int iCRCount = -1;// ")" count
            //int iSLCount = -1;// "[" count
            //int iSRCount = -1;// "]" count
            //int iReturn = 0;

            string strKey;
            string strChar;
            bool blnIsSelf; //check key word is or not included by other word

            returnValue = -1;
            if (strSQl == null || strSQl.Trim() == "")
            {
                return returnValue;
            }

            if (strKeyword == null || strKeyword.Trim() == "")
            {
                return returnValue;
            }
            intPrPos = intStart;
            strKeyword = strKeyword.ToUpper().Trim();
            strKey = strKeyword.Trim().ToUpper();
            strSQl = strSQl.Trim().ToUpper();
            intPos = strSQl.IndexOf(strKey, StringComparison.CurrentCultureIgnoreCase);// Strings.InStr(intStart, strSQl, strKey, CompareMethod.Text);
            if (intPos == -1)
            {
                return returnValue;
            }

            while (intPos >= 0)
            {
                //check key word is or not included by other word
                blnIsSelf = true;
                if (strKey != ",") // "," no need check
                {
                    //check left char , it must be " ",or "()[]" ",.", or key word pos =1
                    if (intPos > 1)
                    {
                        strChar = strSQl.Substring(intPos - 1, 1);
                        if (strChar != " " && strChar != "\n" && strChar != "\t" &&
                           strChar != "," && strChar != "." && strChar != "[" &&
                           strChar != "]" && strChar != "(" && strChar != ")")
                        {
                            blnIsSelf = false;
                        }
                    }
                    //check right char, it must be " ", or key word at last
                    if (blnIsSelf && intPos + strKey.Length - 1 < strSQl.Length)
                    {
                        strChar = strSQl.Substring(intPos + strKey.Length, 1);
                        if (strChar != " " && strChar != "\n" && strChar != "\t" &&
                           strChar != "," && strChar != "." && strChar != "[" &&
                           strChar != "]" && strChar != "(" && strChar != ")")
                        {
                            blnIsSelf = false;
                        }
                    }
                }

                if (!blnIsSelf)
                {
                    //is not a single word, must find again
                    intPos = strSQl.IndexOf(strKey, intPos + 1, StringComparison.CurrentCultureIgnoreCase);  //    (short) Strings.InStr(intPos + 1, strSQl, strKey, CompareMethod.Text);
                }
                else
                {



                    //is a single word, check is or not included by "(" ,")", "[", "]"
                    //calculate "(" ,")", "[", "]" count
                    for (i = intPrPos; i <= intPos - 1; i++)
                    {
                        strChar = strSQl.Substring(i, 1);
                        switch (strChar)
                        {
                            case "(":

                                intCLCount = intCLCount + 1;
                                break;
                            case ")":

                                intCRCount = intCRCount + 1;
                                break;
                            case "[":

                                intSLCount = intSLCount + 1;
                                break;
                            case "]":

                                intSRCount = intSRCount + 1;
                                break;
                            default:

                                break;
                        }
                    }
                    //compare intCLCount and intCRCount,intSLCount and intSRCount
                    //if intCLCount=intCRCount and intSLCount=intSRCount
                    //means find is successfully
                    if (intCLCount == intCRCount && intSLCount == intSRCount)
                    {
                        returnValue = intPos;
                        return returnValue;
                    }
                    else
                    {
                        intPrPos = intPos;
                        intPos = strSQl.IndexOf(strKey, intPos + 1, StringComparison.CurrentCultureIgnoreCase);  // Strings.InStr(intPos + 1, strSQl, strKey, CompareMethod.Text);
                    }
                }
            }
            return returnValue;
        }

        public static int GetSqlKeywordPos(string strSQl, string strKeyword)
        {
            return GetSqlKeywordPos(strSQl, strKeyword, 0);

        }
        #endregion

        #region Get field real and alias from sql
        public static string[] GetFieldRealAndAliasName(string strSql)
        {
            string strMainSql = "";
            string strFieldSql = "";
            string strRealAndAliasName = "";
            int iASPosition  = -1;
            string[] strArrField = null;
            string[] strArrRealAndAliasName=null;
            int i = 0;

            strMainSql = GetSqlParts(strSql, SqlOption.SqlPartMain, ref strArrField);
            //////get rid of "Select" and " from TableName" 
            ////if (strMainSql.IndexOf("select ", StringComparison.CurrentCultureIgnoreCase) != -1)
            ////    strFieldSql = strMainSql.Substring(strMainSql.IndexOf("select ", StringComparison.CurrentCultureIgnoreCase) + 7);
            ////if (strFieldSql.IndexOf(" from ", StringComparison.CurrentCultureIgnoreCase) != -1)
            ////    strFieldSql = strFieldSql.Substring(0, strFieldSql.IndexOf(" from ", StringComparison.CurrentCultureIgnoreCase));
            ////strFieldSql = strFieldSql.Trim();

            iASPosition = strMainSql.IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase);
            if (iASPosition != -1)
            {
                //if field have alias, get FieldRealAndAliasName Array
                for (i=0; i < strArrField.Length; i++)
                {
                    if (strArrField[i].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase) != -1)
                    {
                        strRealAndAliasName+= strArrField[i] + "?";
                    }
                }

                strRealAndAliasName = strRealAndAliasName.TrimEnd('?');
                if (strRealAndAliasName.Trim() != "")
                    strArrRealAndAliasName = strRealAndAliasName.Split('?');
            }

            return strArrRealAndAliasName;
        }
        #endregion

        #region Base Functions
        public static bool IsNumeric(string Value)
        {


            if (Value == null || Value == string.Empty)
            {
                return false;
            }
            else
            {
                if (Value.Substring(0, 1) == "-")
                {
                    Value = Value.Substring(1, Value.Length - 1);
                }

                foreach (char c in Value.ToCharArray())
                {
                    if (!(char.IsNumber(c) || c == '.'))
                    {
                        return false;
                    }
                }
                return true;
            }
        }

        public static int ToInt32(string a_strPara, Int32 a_intDefault)
        {
            if (a_strPara == null) return a_intDefault;
            if (!IsInt(a_strPara)) return a_intDefault;
            try
            {
                return Convert.ToInt32(a_strPara);
            }
            catch
            {
                return a_intDefault;
            }
        }

        public static int ToInt32(Object a_strPara, Int32 a_intDefault)
        {
            if (a_strPara == null) return a_intDefault;
            try
            {
                return Convert.ToInt32(a_strPara);
            }
            catch
            {
                return a_intDefault;
            }
        }

        public static bool IsInt(string Value)
        {
            if (Value == null || Value == string.Empty)
            {
                return false;
            }
            else
            {
                foreach (char c in Value.ToCharArray())
                {
                    if (!char.IsNumber(c))
                        return false;
                }
                return true;
            }
        }
        #endregion

        #region About Data Process

        public void ConnectionDB(string ConnectionString)
        {
            string strConString = "";

            if (ConnectionString.Trim() != "")
                strConString = ConnectionString;
            else
                strConString = "";// DBAccess.ConnectionDBString;

            OpenCon(strConString);
            m_scmCom = new SqlCommand();
            m_scmCom.Connection = m_scnCon;
        }

        public void ConnectionDB(SqlConnection SqlCon)
        {
            if (SqlCon != null)
            {
                //if (SqlCon != null && SqlCon.State != ConnectionState.Open)
                //    SqlCon.Open();

                m_scnCon = SqlCon;
                m_scmCom = new SqlCommand();
                m_scmCom.Connection = m_scnCon;
            }
        }

        public void OpenCon(string ConnectionString)
        {
            m_scnCon = new SqlConnection();
            if (ConnectionString.Trim() != "")
                m_scnCon.ConnectionString = ConnectionString;

            m_scnCon.Open();
        }

        public DataSet GetDataSet(string sql)
        {
            DataSet dataSet = new DataSet();

//            WriteSqlLog(sql);
            m_scmCom.CommandText = sql;
            if (m_sdaAdapter == null)
            {
                m_sdaAdapter = new SqlDataAdapter();
            }
            m_sdaAdapter.SelectCommand = m_scmCom;
            m_sdaAdapter.Fill(dataSet);

           return dataSet;
        }

        public string GetValue(string sql)
        {
            string sRet = "";
            object oRet = null;
//            WriteSqlLog(sql);
            m_scmCom.CommandText = sql;
            oRet = m_scmCom.ExecuteScalar();

            if (oRet == null || oRet == System.DBNull.Value)
            {
                sRet = null;
            }
            else
            {
                sRet = oRet.ToString();
            }

//           CloseCon();

            return sRet;
        }

        public DataRow GetDataFirstRow(string sql)
        {
            DataSet dataSet = new DataSet();

//            WriteSqlLog(sql);
            m_scmCom.CommandText = sql;
            if (m_sdaAdapter == null)
            {
                m_sdaAdapter = new SqlDataAdapter();
            }
            m_sdaAdapter.SelectCommand = m_scmCom;
            m_sdaAdapter.Fill(dataSet);

//            AutoCloseCon();
            if (dataSet.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return dataSet.Tables[0].Rows[0];
            }
        }

        public void ExecuteSql(string sql)
        {
            m_scmCom.CommandText = sql;
            m_scmCom.ExecuteNonQuery();
        }

        public void CloseCon()
        {

            if (m_scnCon != null && m_scnCon.State == ConnectionState.Open)
            {
                m_scnCon.Close();
            }

            if (m_scmCom != null)
            {
                m_scmCom.Dispose();
                m_scmCom = null;
            }

            if (m_sdaAdapter != null)
            {
                m_sdaAdapter.Dispose();
                m_sdaAdapter = null;
            }

            if (m_scnCon != null)
            {
                m_scnCon.Dispose();
                m_scnCon = null;
            }
        }
        #endregion

        #region FormatField
        public static string FormatField(string strField_char, string strFormatStyle)
        {
            int iLength;
            if (strFormatStyle.Length < 1 || strFormatStyle == null)
            {
                return strField_char;
            }
            switch (strFormatStyle.Substring(0, 1).ToUpper())
            {
                case "B":
                    iLength = Convert.ToInt32(strFormatStyle.Substring(1));
                    return FormatWithBlank(strField_char, iLength);
                case "N":
                    return FormatNumeric(strField_char.Trim(), strFormatStyle.Trim());
                case "L":
                    return strField_char.ToLower();
                case "U":
                    return strField_char.ToUpper();
                case "Z":
                    iLength = Convert.ToInt32(strFormatStyle.Substring(1));
                    return FormatWithZero(strField_char, iLength);

                default:
                    return strField_char;
            }

        }
        #endregion

        #region public static string FormatWithBlank()
        public static string FormatWithBlank(string strField_Char, int intField_length)
        {

            string strForm_char = strField_Char.Trim();
            int iDiff = intField_length - strForm_char.Length;

            if (IsNumeric(strForm_char))
            {
                for (int i = 0; i < iDiff; i++)
                {
                    strForm_char = " " + strForm_char;
                }
            }
            return strForm_char.ToUpper();
        }
        #endregion

        #region FormatNumeric:Format numeric according format style ,like as "NN","N.S","NN.N" etc

        //public static string FormatNumeric(object dblData, string strFormatStyle)
        //{
        //    if (dblData == null) dblData = "";
        //    return FormatNumeric(dblData.ToString(), strFormatStyle);
        //}
        //public static string FormatNumeric(double dblData, string strFormatStyle)
        //{
        //    return FormatNumeric(dblData.ToString(), strFormatStyle);
        //}

        public static string FormatNumeric(string strData, string strFormatStyle)
        {
            int intDigitPos = 0;
            string strValue1 = "", strValue2 = "";

            strData = strData.Replace(",", "").Trim();

            //Format the strData to NumerricType
            if (!IsNumeric(strData))
            {
                strData = "0";
            }
            else
            {
                if (strData.Substring(0, 1) == ".")
                    strData = "0" + strData;
                if (strData.Substring(strData.Length - 1, 1) == ".")
                    strData = strData.Substring(0, strData.Length - 1);
            }
            switch (strFormatStyle.ToUpper())
            {
                case "NN"://no format , remove "," in strField_Char  and remove left "0"
                    strData = strData.Replace(",", "");
                    while (strData.Substring(0, 1) == "0" && strData.Length > 1 && strData.Substring(1, 1) != ".")
                    {
                        strData = strData.Substring(1, strData.Length - 1);
                    }
                    return (strData);
                case "N.N"://no format , remove "," in strField_Char  and remove left "0"
                    strData = strData.Replace(",", "");
                    while (strData.Substring(0, 1) == "0" && strData.Length > 1 && strData.Substring(1, 1) != ".")
                    {
                        strData = strData.Substring(1, strData.Length - 1);
                    }
                    return (strData);
                case "NS.N"://'number do not format ,but need add number separator ","
                    strData = strData.Replace(",", "");

                    intDigitPos = strData.IndexOf(".", 0);//get the position of radix point
                    if (intDigitPos > 0)
                    {
                        strValue1 = FormatSeparatorNumeric(strData.Substring(0, intDigitPos));
                        strValue2 = strData.Substring(intDigitPos + 1, strData.Length - intDigitPos - 1);
                        strData = strValue1 + "." + strValue2;
                    }
                    else
                        strData = FormatSeparatorNumeric(strData);
                    return (strData);
                case "NS"://'number do not format ,but need add number separator ","
                    strData = strData.Replace(",", "");

                    intDigitPos = strData.IndexOf(".", 0);//get the position of radix point
                    if (intDigitPos > 0)
                    {
                        strValue1 = FormatSeparatorNumeric(strData.Substring(0, intDigitPos));
                        strValue2 = strData.Substring(intDigitPos + 1, strData.Length - intDigitPos - 1);
                        strData = strValue1 + "." + strValue2;
                    }
                    else
                        strData = FormatSeparatorNumeric(strData);
                    return (strData);
                default:
                    if (strFormatStyle.IndexOf(".") >= 0 && IsNumeric(strFormatStyle.Substring(strFormatStyle.IndexOf(".") + 1)))
                    {
                        strData = decimal.Round(decimal.Parse(strData, System.Globalization.NumberStyles.AllowDecimalPoint | System.Globalization.NumberStyles.AllowLeadingSign), Int32.Parse(strFormatStyle.Substring(strFormatStyle.IndexOf(".") + 1))).ToString();
                    }
                    break;
            }

            strFormatStyle = strFormatStyle.Substring(1, strFormatStyle.Length - 1).ToUpper();
            string strChar = strFormatStyle.Substring(0, 1);

            bool bSp = false;

            if (strChar == "S")
            {
                bSp = true;
                strFormatStyle = strFormatStyle.Substring(1, strFormatStyle.Length - 1);
            }
            else if ((Char.IsNumber(strChar, 0)) || (strChar == ".") || (strChar == ""))
            {
                bSp = false;

            }
            else if (((char.Parse(strChar)) >= 'A' && (char.Parse(strChar)) <= 'R') || ((char.Parse(strChar)) >= 'T' && (char.Parse(strChar)) <= 'Z'))
            {
                bSp = false;
                strFormatStyle = strFormatStyle.Substring(1, strFormatStyle.Length - 1);
            }
            else
            {
                return (strData);
            }

            int intWidth = 0;
            int intScaleWidth = 0;
            // string strAddChar = "";
            int intForLen = 0;

            intDigitPos = strFormatStyle.IndexOf(".");

            if (intDigitPos < 0)
            {
                intWidth = Int32.Parse(strFormatStyle);
                intScaleWidth = 0;
            }
            else if (intDigitPos == 0)
            {
                intWidth = 0;
                intScaleWidth = Int32.Parse(strFormatStyle.Substring(1, strFormatStyle.Length - 1)); ;
            }
            else if (intDigitPos >= 1)
            {
                intWidth = Int32.Parse(strFormatStyle.Substring(0, intDigitPos));
                intScaleWidth = Int32.Parse(strFormatStyle.Substring(intDigitPos + 1));
            }


            //Add scale
            if (intScaleWidth > 0)
            {
                intDigitPos = strData.IndexOf(".");
                if (intDigitPos >= 0)
                {
                    strValue2 = strData.Substring(intDigitPos + 1, strData.Length - intDigitPos - 1);
                }
                else
                {
                    strData = strData + ".";
                    strValue2 = "";
                }
                intForLen = intScaleWidth - strValue2.Length;
                if (intForLen > 0)
                {
                    for (int i = 0; i < intForLen; i++)
                    {
                        strData += "0";
                    }
                }

            }

            //Add spearater
            if (bSp)
            {
                strData = strData.Replace(",", "");
                intDigitPos = strData.IndexOf(".");
                if (intDigitPos >= 0)
                {
                    strValue1 = FormatSeparatorNumeric(strData.Substring(0, intDigitPos));
                    strValue2 = strData.Substring(intDigitPos + 1, strData.Length - intDigitPos - 1);
                    strData = strValue1 + "." + strValue2;

                }
                else
                {
                    strData = FormatSeparatorNumeric(strData);
                }
            }
            return strData;

        }

        private static string FormatSeparatorNumeric(string strData)
        {
            string StrTemp = "";
            int iIndex = 0;
            int intLen = strData.Length;
            for (int i = intLen - 1; i >= 0; i--)
            {
                iIndex++;
                StrTemp = strData.Substring(i, 1) + StrTemp;
                if (iIndex % 3 == 0)
                    StrTemp = "," + StrTemp;
            }

            if (StrTemp[0] == ',')
            {
                StrTemp = StrTemp.Substring(1, StrTemp.Length - 1);
            }
            return (StrTemp);
        }

        private static int GetIntFromStr(string strData)
        {
            string strTemp = "";
            int intNum = 0;
            int intLen = strData.Length;
            for (int i = 0; i < intLen; i++)
            {
                if (Char.IsNumber(strData[i]))
                {
                    strTemp += strData.Substring(i, 1);
                }
            }

            if (strTemp != "")
            {
                intNum = int.Parse(strTemp);
            }
            return (intNum);
        }
        #endregion

        #region public static string FormatWithZero()
        public static string FormatWithZero(string strField_Char, int intField_length)
        {

            string strForm_char = strField_Char.Trim();
            int iDiff = intField_length - strForm_char.Length;

            if (IsNumeric(strForm_char))
            {
                for (int i = 0; i < iDiff; i++)
                {
                    strForm_char = "0" + strForm_char;
                }
            }
            return strForm_char.ToUpper();
        }
        #endregion

        #region GetmSwitchValue: Get switch bool value from mSwitch table according to Modual name and swtich name
        /// <summary>
        /// Get switch value form mMiscSwitch table according to a Modual name and a swtich name
        /// </summary>
        /// <param name="strSwitchName">Switch Name</param>
        /// <param name="strModualName">Module name</param>
        /// <returns>switch value,if null false ""</returns>
        public static bool GetmSwitchValue(ComFunc comFunc, string strSwitchName, string strModualName)
        {
            string strSql = string.Empty;
            bool bReturn = false;
            string strErrorMessage = string.Empty;
            string strReturn = "";

            strSql = "Select switchValue from mSwitch where SwitchSys='{0}' and SwitchName='{1}'";
            strSql = string.Format(strSql, strModualName, strSwitchName);

            strReturn = comFunc.GetValue(strSql);
            if (strReturn == null)
            {
                bReturn = false;
                //UpdatemMissSwitch(strSwitchName, strModualName);
            }
            else
            {
                bReturn = bool.Parse(strReturn);
            }
            return bReturn;
        }
        #endregion

        #region GetmMiscValue: Get switch string value from mMiscSwitch table according to Modual name and swtich name
        /// <summary>
        /// Get switch value form mMiscSwitch table according to a Modual name and a swtich name
        /// </summary>
        /// <param name="strSwitchName">Switch Name</param>
        /// <param name="strModualName">Module name</param>
        /// <returns>switch value,if null return ""</returns>
        public static string GetmMiscValue(ComFunc dBAccess, string strSwitchName, string strModualName)
        {
            string strSql = string.Empty;
            string strReturn = string.Empty;
            string strType = string.Empty;
            string strErrorMessage = string.Empty;
            DataSet dataSet = new DataSet();
            ComFunc dbAccess = null;

            if (dBAccess == null)
                dbAccess = new ComFunc();
            else
                dbAccess = dBAccess;

            strSql = "SELECT switchValue,Type FROM mMiscSwitch WHERE SystemName='{0}' AND SwitchName='{1}'";
            strSql = string.Format(strSql, strModualName, strSwitchName);
            dataSet = dbAccess.GetDataSet(strSql);

            if (dataSet.Tables[0].Rows.Count > 0)
            {
                DataRow DR = dataSet.Tables[0].Rows[0];
                strReturn = DR[0].ToString().TrimEnd();
                strType = DR[1].ToString().TrimEnd().ToLower();
                switch (strType)
                {
                    case "numeric":
                        strReturn = strReturn.Replace(",", "").Trim();
                        if (!ComFunc.IsNumeric(strReturn))
                        {
                            strReturn = "";
                            strErrorMessage = "The switch " + strSwitchName + " setting error,It must be numeric type.";
                        }
                        break;
                    case "char":
                        strReturn = strReturn.TrimEnd();

                        break;
                    case "date":
                        if (!ComFunc.IsDate(strReturn))
                        {
                            strErrorMessage = "Invalid date.The switch " + strSwitchName + " setting error.";
                            strReturn = DateTime.Now.ToShortDateString();
                        }
                        break;
                    default:
                        strReturn = string.Empty;
                        break;
                }
                if (strErrorMessage != string.Empty)
                {
                    throw new Exception(strErrorMessage);
                }
            }
            else
            {
                UpdatemMissSwitch(strSwitchName, strModualName);
                strReturn = "";
            }

            if (dBAccess == null)
                dbAccess.CloseCon();
            return strReturn;
        }

        public static string GetmMiscValue(string strSwitchName, string strModualName)
        {
            return GetmMiscValue(null, strSwitchName, strModualName);
        }

        public static string GetmMiscValue(string strSwitchName)
        {
            return GetmMiscValue(null, strSwitchName, "");
        }

        private static void UpdatemMissSwitch(string strSwitchName, string strModualName)
        {
            string strSql = "";
            int iCount = 0;
            return;
            ComFunc dBAccess = new ComFunc();

            strSql = "SELECT COUNT(*) FROM mMissSwitch WHERE SwitchSys='" + strModualName + "' AND SwitchName='" + strSwitchName + "'";
            iCount = int.Parse(dBAccess.GetValue(strSql));
            if (iCount == 0)
            {
                strSql = "INSERT INTO mMissSwitch (SwitchSys,SwitchName) VALUES('" + strModualName + "','" + strSwitchName + "')";
                dBAccess.ExecuteSql(strSql);
            }

            dBAccess.CloseCon();
        }
        #endregion

        public static int GetRecIDSeqNo(long lRecID, string strTableName, string strSql, string strOrderBy, string strOuterCondition, ref int iRowCounts)
        {
            ComFunc dbcon = new ComFunc();
            int i = GetRecIDSeqNo(dbcon, lRecID, strTableName, strSql, strOrderBy, strOuterCondition, ref iRowCounts);
            dbcon.CloseCon();
            return i;
        }
        public static int GetRecIDSeqNo(ComFunc dbcon, long lRecID, string strTableName, string strSql, string strOrderBy, string strOuterCondition, ref int iRowCounts)
        {
            if (lRecID == -1) return 0;
            //string strSql = WP.Commons.BaseFunctions.GetLookUpSQL(strTableName);
            string strOrderByField = strOrderBy.Replace("[","").Replace("]","");//GetSqlParts(strSql, SqlOption.SqlPartOrderBy);
            string strOrgWhereCondition = GetSqlParts(strSql, SqlOption.SqlPartWhere);
            if (strOrderByField.Trim() == "") strOrderByField = " Order By RECID";

            strOrderByField = strOrderByField.TrimEnd().ToUpper().Replace(" ORDER BY ", "");
            string strKeyFields = GetTablePrimaryKeyFields(dbcon, strTableName);
            strKeyFields = strKeyFields.Replace("[", "").Replace("]", "");
            string[] OrderByFieldsArray = strOrderByField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string[] KeyFieldsArray = strKeyFields.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            bool blnOrderByFieldsIncludeKeyFields = false;
            int iSeqNo = 0;

            foreach (string keyf in KeyFieldsArray)
            {
                blnOrderByFieldsIncludeKeyFields = false;
                foreach (string orderf in OrderByFieldsArray)
                {
                    string f = orderf.Trim();
                    if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                    if (string.Compare(f.TrimEnd(), keyf.TrimEnd(), true) == 0)
                    {
                        blnOrderByFieldsIncludeKeyFields = true;
                        break;
                    }
                }
            }
            if (KeyFieldsArray.Length == 0 && strOrderByField.IndexOf("Recid", StringComparison.CurrentCultureIgnoreCase) == -1)
            {
                strOrderByField += ",RecID";
            }
            else if (!blnOrderByFieldsIncludeKeyFields)
            {
                foreach (string keyf in KeyFieldsArray)
                {
                    blnOrderByFieldsIncludeKeyFields = false;
                    foreach (string orderf in OrderByFieldsArray)
                    {
                        string f = orderf.Trim();
                        if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                        if (string.Compare(f.TrimEnd(), keyf.TrimEnd(), true) == 0)
                        {
                            blnOrderByFieldsIncludeKeyFields = true;
                            break;
                        }
                    }
                    if (!blnOrderByFieldsIncludeKeyFields) strOrderByField += "," + keyf;
                }
            }
            OrderByFieldsArray = strOrderByField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string strCondtion = "", strValue = "", strField = "", strCaseCondition = "";
            DataRow dr = dbcon.GetDataFirstRow("select top 1 * from " + strTableName + " where RecId=" + lRecID.ToString());

            if (dr != null)
            {

                #region Prev record

                for (int i = 0; i < OrderByFieldsArray.Length; i++)
                {
                    string f = OrderByFieldsArray[i].Trim().ToUpper();
                    if (f.IndexOf(" ASC", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        f = f.Replace(" ASC", " DESC");
                    }
                    else if (f.IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        f = f.Replace(" DESC", " ASC");
                    }
                    else
                    {
                        f += " DESC";
                    }
                    OrderByFieldsArray[i] = f;
                }

                foreach (string keyf in OrderByFieldsArray)
                {

                    string strSign = "";
                    string f = keyf.Trim().ToUpper();
                    if (f.IndexOf(" ASC", StringComparison.OrdinalIgnoreCase) >= 0)
                        strSign = ">";
                    else if (f.IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) >= 0)
                        strSign = "<";
                    else
                        strSign = ">";
                    strValue = "";
                    if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                    string strDateType = dr.Table.Columns[f].DataType.Name;
                    if (strDateType == "String")
                    {
                        strField = f;
                        strValue += dr[f].ToString().Replace("'", "''");
                        strSign += "'";
                        strValue += "'";
                    }
                    else if (strDateType == "DateTime")
                    {
                        strField = "rtrim(convert(char(10), isnull(" + f + "," + "'" + "'" + "),111))";
                        if (IsDate(dr[f].ToString()))
                            strValue = FormatDate(dr[f].ToString(), "yyyy/MM/dd");
                        else
                            strValue += "";
                        strSign += "'";
                        strValue += "'";
                    }
                    else if (strDateType == "Int32" || strDateType == "Decimal")
                    {
                        strField = f;
                        strValue = dr[f].ToString().TrimEnd();
                    }
                    else if (strDateType == "Boolean")
                    {
                        strField = f;
                        strValue = (dr[f].ToString().TrimEnd().ToUpper()=="FALSE"?"0":"1");
                    }
                    else
                    {
                        strField = f;
                        strValue = dr[f].ToString().TrimEnd();
                    }
                    if (strCaseCondition != "")
                        strCondtion += " or (" + strCaseCondition + " and " + strField + strSign + strValue + ")";
                    else
                        strCondtion += "(" + strField + strSign + strValue + ")";

                    strCaseCondition += (strCaseCondition != "" ? " and " : "") + strField + "=" + (strDateType == "String" || strDateType == "DateTime" ? "'" : "") + strValue;
                }
                strCondtion = " (" + strCondtion + ")";

                #endregion

                strOrderByField = "";
                for (int i = 0; i < OrderByFieldsArray.Length; i++)
                {
                    strOrderByField += OrderByFieldsArray[i];
                    if (i + 1 < OrderByFieldsArray.Length) strOrderByField += ",";
                }

                strCondtion = "( RecID<>" + lRecID.ToString() + (strCondtion != "" ? " and " + strCondtion : "") + ")";

                #region calc row counts
                string strWhere = "";
                if (strOrgWhereCondition.Trim() != "")
                    strWhere = strOrgWhereCondition + (strOuterCondition != "" ? " and " + strOuterCondition : "");
                else
                    strWhere = (strOuterCondition != "" ? " where " + strOuterCondition : "");

                DataRow drCount = dbcon.GetDataFirstRow("select count(*) as count from " + strTableName + strWhere);
                if (drCount != null)
                    iRowCounts = Convert.ToInt32(drCount[0].ToString());
                #endregion

                if (strOuterCondition.Trim() != "")
                    strCondtion = "( " + strOuterCondition + (strCondtion != "" ? " and " + strCondtion : "") + ")";

                if (strOrgWhereCondition.Trim() != "")
                    strCondtion = strOrgWhereCondition + (strCondtion != "" ? " and " + strCondtion : "");
                else if (strCondtion != "")
                    strCondtion = " where " + strCondtion;

                strSql = "select 1 from " + strTableName + strCondtion + (strOrderByField != "" ? " Order By " + strOrderByField : "");
                iSeqNo = dbcon.GetDataSet(strSql).Tables[0].Rows.Count + 1;
            }
            else
                iSeqNo = 1;

            return iSeqNo;
        }
        public static int GetFieldSeqNo(ComFunc dbcon, string strFieldName, string strFieldValue, string strTableName, string strSql, string strOrderBy, string strOuterCondition, ref int iRowCounts)
        {
            if (strFieldValue == string.Empty) return 1;
            string strExceptOrderBySql = string.Empty;
            string strOrderBySql = string.Empty;
            string strOrdByField = string.Empty;

            strFieldValue = strFieldValue.Replace("'", "''");
            strOrderBySql = ComFunc.GetSqlParts(strSql, ComFunc.SqlOption.SqlPartOrderBy);
            // if find field not in Order by field and order by field is only one, use order by field
            if (strOrderBySql != string.Empty && strFieldName.ToUpper() != strOrderBySql.TrimEnd().ToUpper().Replace(" ORDER BY ", "") && strOrderBySql.TrimEnd().ToUpper().Replace(" ORDER BY ", "").Split(',').Length == 1)
            {
                strOrdByField = strOrderBySql.TrimEnd().ToUpper().Replace(" ORDER BY ", "").Trim();
                if (strOrdByField.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries).Length > 1) // at 10/15/2008 remove TableName(eg: A.Field --> Field)
                    strOrdByField = strOrdByField.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries)[1];
            }
            else
                strOrdByField = strFieldName;

            if (strOrderBy.Trim() != string.Empty)
                strOrdByField = strOrderBy.Replace("[", "").Replace("]", "");

            if (strOrderBySql != string.Empty)
            {
                strExceptOrderBySql = strSql.Substring(strSql.ToUpper().IndexOf(strOrderBySql, StringComparison.CurrentCultureIgnoreCase), strOrderBySql.Length);
                strExceptOrderBySql = strSql.Replace(strExceptOrderBySql, "");
            }
            else
                strExceptOrderBySql = strSql;

            string strOrderByField = strOrderBy;//.Replace("[", "").Replace("]", "");//GetSqlParts(strSql, SqlOption.SqlPartOrderBy);
            //string strOrgWhereCondition = GetSqlParts(strSql, SqlOption.SqlPartWhere);
            if (strOrderByField.Trim() == "") strOrderByField = " Order By RECID";

            strOrderByField = strOrderByField.TrimEnd().ToUpper().Replace(" ORDER BY ", "");
            string strKeyFields = GetTablePrimaryKeyFields(dbcon, strTableName);
            string[] OrderByFieldsArray = strOrderByField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string[] KeyFieldsArray = strKeyFields.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            bool blnOrderByFieldsIncludeKeyFields = false;
            //string strFieldRealName = "";
            string strFieldAliasName = "";

            int iSeqNo = 0;

            foreach (string keyf in KeyFieldsArray)
            {
                blnOrderByFieldsIncludeKeyFields = false;
                foreach (string orderf in OrderByFieldsArray)
                {
                    string f = orderf.Trim();
                    if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                    if (string.Compare(f.TrimEnd(), keyf.TrimEnd(), true) == 0)
                    {
                        blnOrderByFieldsIncludeKeyFields = true;
                        break;
                    }
                }
            }
            if (KeyFieldsArray.Length == 0 && strOrderByField.IndexOf("Recid", StringComparison.CurrentCultureIgnoreCase) == -1)
            {
                strOrderByField += ",[RecID]";
            }
            else if (!blnOrderByFieldsIncludeKeyFields)
            {
                foreach (string keyf in KeyFieldsArray)
                {
                    blnOrderByFieldsIncludeKeyFields = false;
                    foreach (string orderf in OrderByFieldsArray)
                    {
                        string f = orderf.Trim();
                        if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                        if (string.Compare(f.TrimEnd(), keyf.TrimEnd(), true) == 0)
                        {
                            blnOrderByFieldsIncludeKeyFields = true;
                            break;
                        }
                    }
                    if (!blnOrderByFieldsIncludeKeyFields) strOrderByField += "," + keyf;
                }
            }

            #region add not exist key field in sql list
            string strMainSelectSql = ComFunc.GetSqlParts(strExceptOrderBySql, ComFunc.SqlOption.SqlPartMain);
            for (int j = 0; j < KeyFieldsArray.Length; j++)
            {
                if (strMainSelectSql.IndexOf(KeyFieldsArray[j].Trim(), StringComparison.CurrentCultureIgnoreCase) == -1 &&
                    strMainSelectSql.IndexOf(strTableName + "." + KeyFieldsArray[j].Trim().Replace("[", "").Replace("]", ""), StringComparison.CurrentCultureIgnoreCase) == -1)
                {
                    strExceptOrderBySql = strExceptOrderBySql.Insert(strExceptOrderBySql.IndexOf(" from ", StringComparison.CurrentCultureIgnoreCase), "," + KeyFieldsArray[j].Trim().Replace("[", "").Replace("]", ""));
                }
            }
            #endregion

            #region replace the order by field use alias
            strOrderByField = DealWithAlias(strSql, strOrderByField);
            #endregion

            OrderByFieldsArray = strOrderByField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string strCondtion = "", strValue = "", strField = "", strCaseCondition = "", strDateTypeCon = string.Empty;
            //DataRow dr = dbcon.GetDataFirstRow("select top 1 * from " + strTableName + (strOrgWhereCondition == "" ? " where " : strOrgWhereCondition + " and ") + strFieldName + ">='" + strFieldValue + "' order by " + strFieldName);
            strFieldAliasName = ReturnSqlAliasName(strSql, strFieldName);
            if (strFieldAliasName == string.Empty)
                strFieldAliasName = strFieldName;
            //DataRow dr = dbcon.GetDataFirstRow("select top 1 * from (" + strExceptOrderBySql + ") as t where " + strFieldAliasName + ">='" + strFieldValue + "' order by " + strOrdByField);
            DataRow dr = dbcon.GetDataFirstRow("select top 1 * from (" + strExceptOrderBySql + ") as t where " + strFieldAliasName + " like '" + strFieldValue + "%' order by " + strOrdByField);

            if (dr != null)
            {
                #region Prev record

                for (int i = 0; i < OrderByFieldsArray.Length; i++)
                {
                    string f = OrderByFieldsArray[i].Trim().ToUpper();
                    if (f.IndexOf(" ASC", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        f = f.Replace(" ASC", " DESC");
                    }
                    else if (f.IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        f = f.Replace(" DESC", " ASC");
                    }
                    else
                    {
                        f += " DESC";
                    }
                    OrderByFieldsArray[i] = f;
                }

                foreach (string keyf in OrderByFieldsArray)
                {

                    string strSign = "";
                    string f = keyf.Trim().ToUpper();

                    if (f.IndexOf(" ASC", StringComparison.OrdinalIgnoreCase) >= 0)
                        strSign = ">";
                    else if (f.IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) >= 0)
                        strSign = "<";
                    else
                        strSign = ">";
                    strValue = "";
                    if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                    f = f.Replace("[", "").Replace("]", "");

                    if (dr.Table.Columns[f] == null) break;
                    string strDateType = dr.Table.Columns[f].DataType.Name;
                    if (strDateType == "String")
                    {
                        strField = f;
                        strValue += dr[f].ToString().Replace("'", "''");
                        strSign += "'";
                        strValue += "'";
                    }
                    else if (strDateType == "DateTime")
                    {
                        strField = "rtrim(convert(char(10), isnull(" + f + "," + "'" + "'" + "),111))";
                        if (IsDate(dr[f].ToString()))
                            strValue = FormatDate(dr[f].ToString(), "yyyy/MM/dd");
                        else
                            strValue += "";
                        strSign += "'";
                        strValue += "'";
                    }
                    else if (strDateType == "Int32" || strDateType == "Decimal")
                    {
                        strField = f;
                        strValue = dr[f].ToString().TrimEnd() == "" ? "null" : dr[f].ToString().TrimEnd();
                    }
                    else if (strDateType == "Boolean")
                    {
                        strField = f;
                        strValue = (dr[f].ToString().TrimEnd().ToUpper() == "FALSE" ? "0" : "1");
                    }
                    else
                    {
                        strField = f;
                        strValue = dr[f].ToString().TrimEnd();
                    }
                    if (strCaseCondition != "")
                        strCondtion += " or (" + strCaseCondition + " and " + strField + strSign + strValue + ")";
                    else
                        strCondtion += "(" + strField + strSign + strValue + ")";

                    strCaseCondition += (strCaseCondition != "" ? " and " : "") + strField + "=" + (strDateType == "String" || strDateType == "DateTime" ? "'" : "") + strValue;
                    if (strDateType == "DateTime" && strSign.IndexOf("<", StringComparison.OrdinalIgnoreCase) >= 0)
                        strDateTypeCon += " or " + f + " is null ";
                }
                strCondtion = " (" + strCondtion + ")";

                #endregion

                //strOrderByField = "";
                //for (int i = 0; i < OrderByFieldsArray.Length; i++)
                //{
                //    if (strArrRealAndAliasName != null)
                //    {
                //        strFieldRealName = "";
                //        for (int j = 0; j < strArrRealAndAliasName.Length; j++)
                //        {
                //            strFieldRealName = strArrRealAndAliasName[j].Substring(0, strArrRealAndAliasName[j].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase)).ToUpper();
                //            strFieldAliasName = strArrRealAndAliasName[j].Substring(strArrRealAndAliasName[j].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase) + 4).ToUpper();
                //            //strOrderByField = strOrderByField.Replace(strFieldAliasName, strFieldRealName);
                //            strFieldRealName = strFieldRealName.Substring(0, 1) == "[" ? strFieldRealName : "[" + strFieldRealName + "]";
                //            strFieldAliasName = strFieldAliasName.Substring(0, 1) == "[" ? strFieldAliasName : "[" + strFieldAliasName + "]";
                //            if (OrderByFieldsArray[i].Split(' ')[0].Substring(0, 1) != "[")
                //            {
                //                if ("[" + OrderByFieldsArray[i].Split(' ')[0] + "]" == strFieldAliasName)
                //                    OrderByFieldsArray[i] = Convert.ToString("[" + OrderByFieldsArray[i].Split(' ')[0] + "]").Replace(strFieldAliasName, strFieldRealName).TrimStart('[').TrimEnd(']') + " " + OrderByFieldsArray[i].Split(' ')[1];
                //            }
                //        }
                //    }
                //    strOrderByField += OrderByFieldsArray[i];
                //    if (i + 1 < OrderByFieldsArray.Length) strOrderByField += ",";
                //}

                if (strDateTypeCon != string.Empty)
                    strCondtion = "((" + strFieldAliasName + "<>'" + strFieldValue + "'" + strDateTypeCon + ")" + (strCondtion != "" ? " and " + strCondtion : "") + ")";
                else
                    strCondtion = "( " + strFieldAliasName + "<>'" + strFieldValue + "'" + (strCondtion != "" ? " and " + strCondtion : "") + ")";

                #region calc row counts
                string strWhere = "";
                //if (strOrgWhereCondition.Trim() != "")
                //    strWhere = strOrgWhereCondition + (strOuterCondition != "" ? " and " + strOuterCondition : "");
                //else
                    strWhere = (strOuterCondition != "" ? " where " + strOuterCondition : "");

                //DataRow drCount = dbcon.GetDataFirstRow("select count(*) as count from " + strTableName + strWhere);
                DataRow drCount = dbcon.GetDataFirstRow("select count(*) as count from (" + strExceptOrderBySql + ") as t " + strWhere);
                if (drCount != null)
                    iRowCounts = Convert.ToInt32(drCount[0].ToString());
                #endregion

                if (strOuterCondition.Trim() != "")
                    strCondtion = "( " + strOuterCondition + (strCondtion != "" ? " and " + strCondtion : "") + ")";

                //if (strOrgWhereCondition.Trim() != "")
                //    strCondtion = strOrgWhereCondition + (strCondtion != "" ? " and " + strCondtion : "");
                //else 
                    if (strCondtion != "")
                        strCondtion = " where " + strCondtion;

                //strSql = "select 1 from " + strTableName + strCondtion + (strOrderByField != "" ? " Order By " + strOrderByField : "");
                strSql = "select 1 from (" + strExceptOrderBySql + ") as t " + strCondtion + (strOrderByField != "" ? " Order By " + strOrderByField : "");
                iSeqNo = dbcon.GetDataSet(strSql).Tables[0].Rows.Count + 1;
            }
            else
                iSeqNo = 1;

            return iSeqNo;
        }


        public static long GetFindRecID(ComFunc dbcon, string strTableName, string strSql, string strOrderBy, string strOuterCondition, ref PageInfo scPageInfo)
        {
            //if (strFieldValue == string.Empty) return -1;
            string strExceptOrderBySql = string.Empty;
            string strOrderBySql = string.Empty;
            DataRow dr = null;
            DataRow dr_LastPageBegin = null;
            DataRow dr_LastPageEnd = null;
            DataTable dt = null;
            long lLastPageBeginRecNo = -1;
            long lLastPageEndRecNo = -1;
            long lRecNo = -1;
            string strCondition = string.Empty;
            string[] OrderByFieldsArray_Org;

            string strSuitCon = string.Empty;
            string strTableSuitCon = string.Empty;
            long lRecID = -1;
            PageOrient nOrient = PageOrient.None;
            string strFieldName = string.Empty;
            string strFieldValue = string.Empty; 

            lRecID = scPageInfo.lBeginRecID;
            nOrient = scPageInfo.nOrient;
            strFieldName = scPageInfo.strFindField;
            strFieldValue = scPageInfo.strFindValue;

            strFieldValue = strFieldValue.Replace("'", "''");
            strOrderBySql = ComFunc.GetSqlParts(strSql, ComFunc.SqlOption.SqlPartOrderBy);
            if (strOrderBySql != string.Empty)
            {
                strExceptOrderBySql = strSql.Substring(strSql.ToUpper().IndexOf(strOrderBySql, StringComparison.CurrentCultureIgnoreCase), strOrderBySql.Length);
                strExceptOrderBySql = strSql.Replace(strExceptOrderBySql, "");
            }
            else
                strExceptOrderBySql = strSql;

            string strOrderByField = strOrderBy;
            ////if (strOrderByField.Trim() == "") strOrderByField = " Order By RECID";

            strOrderByField = strOrderByField.TrimEnd().ToUpper().Replace(" ORDER BY ", "");
            string strKeyFields = GetTablePrimaryKeyFields(dbcon, strTableName);
            if (strTableName.ToUpper() == "#ItemData".ToUpper()) { strKeyFields = "[ItemNo],[LocationNo]"; }
            string[] OrderByFieldsArray = strOrderByField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            string[] KeyFieldsArray = strKeyFields.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            bool blnOrderByFieldsIncludeKeyFields = false;
            string strFieldAliasName = "";

            foreach (string keyf in KeyFieldsArray)
            {
                blnOrderByFieldsIncludeKeyFields = false;
                foreach (string orderf in OrderByFieldsArray)
                {
                    string f = orderf.Trim();
                    if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                    if (string.Compare(f.TrimEnd(), keyf.TrimEnd(), true) == 0)
                    {
                        blnOrderByFieldsIncludeKeyFields = true;
                        break;
                    }
                }
            }
            if (KeyFieldsArray.Length == 0 && strOrderByField.IndexOf("Recid", StringComparison.CurrentCultureIgnoreCase) == -1)
            {
                strOrderByField += ",[RecID]";
            }
            else if (!blnOrderByFieldsIncludeKeyFields)
            {
                foreach (string keyf in KeyFieldsArray)
                {
                    blnOrderByFieldsIncludeKeyFields = false;
                    foreach (string orderf in OrderByFieldsArray)
                    {
                        string f = orderf.Trim();
                        if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                        if (string.Compare(f.TrimEnd(), keyf.TrimEnd(), true) == 0)
                        {
                            blnOrderByFieldsIncludeKeyFields = true;
                            break;
                        }
                    }
                    if (!blnOrderByFieldsIncludeKeyFields) strOrderByField += "," + keyf;// +" ASC";
                }
            }

            #region add not exist key field in sql list
            string strMainSelectSql = ComFunc.GetSqlParts(strExceptOrderBySql, ComFunc.SqlOption.SqlPartMain);
            for (int j = 0; j < KeyFieldsArray.Length; j++)
            {
                if (strMainSelectSql.IndexOf(KeyFieldsArray[j].Trim(), StringComparison.CurrentCultureIgnoreCase) == -1)
                {
                    strExceptOrderBySql = strExceptOrderBySql.Insert(strExceptOrderBySql.IndexOf(" from ", StringComparison.CurrentCultureIgnoreCase), "," + KeyFieldsArray[j].Trim().Replace("[", "").Replace("]", ""));
                }
            }
            #endregion

            #region replace the order by field use alias
            strOrderByField = DealWithAlias(strSql, strOrderByField);
            #endregion
            ////if (scPageInfo.strFindValue != string.Empty)
            ////{
            ////    strOrderByField = strOrderByField.ToUpper().Replace(" DESC", " ASC");
            ////}

            OrderByFieldsArray = strOrderByField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            OrderByFieldsArray_Org = new string[OrderByFieldsArray.Length];
            OrderByFieldsArray.CopyTo(OrderByFieldsArray_Org, 0);
            ////OrderByFieldsArray_Org = OrderByFieldsArray;
            //string strCondtion = "", 
            strFieldAliasName = ReturnSqlAliasName(strSql, strFieldName);
            if (strFieldAliasName == string.Empty)
                strFieldAliasName = strFieldName;

            if (lRecID != -1)
            {
                if (scPageInfo.strPrimaryKeyCon.Trim() != string.Empty)
                    dr = dbcon.GetDataFirstRow("select top 1 * from (" + strExceptOrderBySql + ") as t " + " where " + scPageInfo.strPrimaryKeyCon);
                else
                    dr = dbcon.GetDataFirstRow("select top 1 * from (" + strExceptOrderBySql + ") as t " + " where RecId=" + lRecID.ToString());
            }
            else
                dr = dbcon.GetDataFirstRow("select top 1 * from (" + strExceptOrderBySql + ") as t " + (strFieldAliasName != "" ? "where " + strFieldAliasName + " like '" + strFieldValue + "%' order by " + strFieldAliasName : " order by " + strOrderByField));
            //dr = dbcon.GetDataFirstRow("select top 1 * from (" + strExceptOrderBySql + ") as t " + (strFieldAliasName != "" ? "where " + strFieldAliasName + ">='" + strFieldValue + "' order by " + strFieldAliasName : " order by " + strOrderByField));

            if (dr != null)
            {
                #region Get RecID
                switch (nOrient)
                {
                    case PageOrient.Top:
                        break;
                    case PageOrient.Prev:
                    case PageOrient.PageUp:
                        #region Prev record

                        for (int i = 0; i < OrderByFieldsArray.Length; i++)
                        {
                            string f = OrderByFieldsArray[i].Trim().ToUpper();
                            if (f.IndexOf(" ASC", StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                f = f.Replace(" ASC", " DESC");
                            }
                            else if (f.IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                f = f.Replace(" DESC", " ASC");
                            }
                            else
                            {
                                f += " DESC";
                            }
                            OrderByFieldsArray[i] = f;
                        }

                        strCondition = GetCondition(OrderByFieldsArray, dr);

                        break;
                        #endregion
                    case PageOrient.Next:
                    case PageOrient.PageDown:
                        #region Next record
                        strCondition = GetCondition(OrderByFieldsArray, dr, false, scPageInfo);
                        break;
                        #endregion
                    case PageOrient.Bottom:
                        #region Bottom
                        for (int i = 0; i < OrderByFieldsArray.Length; i++)
                        {
                            string f = OrderByFieldsArray[i].Trim().ToUpper();
                            if (f.IndexOf(" ASC", StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                f = f.Replace(" ASC", " DESC");
                            }
                            else if (f.IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                f = f.Replace(" DESC", " ASC");
                            }
                            else
                            {
                                f += " DESC";
                            }
                            OrderByFieldsArray[i] = f;
                        }
                        break;
                        #endregion
                    case PageOrient.None:
                        #region None
                        strCondition = GetCondition(OrderByFieldsArray, dr, false, scPageInfo);
                        break;
                        #endregion
                }
                strOrderByField = "";
                for (int i = 0; i < OrderByFieldsArray.Length; i++)
                {
                    strOrderByField += OrderByFieldsArray[i];
                    if (i + 1 < OrderByFieldsArray.Length) strOrderByField += ",";
                }
                if (nOrient == PageOrient.Prev || nOrient == PageOrient.Next)
                {
                    strCondition = "( RecID<>" + lRecID.ToString() + (strCondition != "" ? " and " + strCondition : "") + ")";
                }

                if (strOuterCondition.Trim() != "")
                    strCondition = "( " + strOuterCondition + (strCondition != "" ? " and " + strCondition : "") + ")";

                if (strCondition != "")
                    strCondition = " where " + strCondition;

                string strOrderByField_Opp = string.Empty;
                if (nOrient == PageOrient.Prev || nOrient == PageOrient.PageUp || nOrient == PageOrient.Top || nOrient == PageOrient.Bottom)
                    strOrderByField_Opp = strOrderByField;
                else
                    strOrderByField_Opp = strOrderByField.Replace(" ASC", " ###").Replace(" DESC", " ASC").Replace(" ###", " DESC");
                strSql = "select top " + (scPageInfo.iRowsPerPage + 1) + (strKeyFields.Trim() != string.Empty ? " RecID," + strKeyFields : " RecID") + " from (" + strExceptOrderBySql + ") as t " + (strOrderByField_Opp != "" ? " Order By " + strOrderByField_Opp : "");
                dt = dbcon.GetDataSet(strSql).Tables[0];
                if (dt.Rows.Count == 0)
                    lLastPageBeginRecNo = -1;
                else if (dt.Rows.Count < scPageInfo.iRowsPerPage)
                {
                    lLastPageBeginRecNo = -1;
                    //lRecNo = -1;
                    //scPageInfo.lBeginRecID = scPageInfo.lBeginRecID_Last;
                    //scPageInfo.strTableSuitCon = scPageInfo.strTableSuitCon_Last;
                    //return lRecNo;
                }
                else
                {
                    lLastPageEndRecNo = Convert.ToInt64(dt.Rows[0]["RecID"]);
                    lLastPageBeginRecNo = Convert.ToInt64(dt.Rows[dt.Rows.Count - 2]["RecID"]);
                    dr_LastPageEnd = dt.Rows[0];
                    if (nOrient == PageOrient.PageDown || nOrient == PageOrient.PageUp)
                        dr_LastPageBegin = dt.Rows[dt.Rows.Count - 1];
                    else
                        dr_LastPageBegin = dt.Rows[dt.Rows.Count - 2];
                }


                if (nOrient == PageOrient.PageDown || nOrient == PageOrient.PageUp)
                {
                    int iRowCount = (nOrient == PageOrient.PageDown ? 2 * scPageInfo.iRowsPerPage : scPageInfo.iRowsPerPage);
                    strSql = "select top " + iRowCount + " * from (" + strExceptOrderBySql + ") as t " + (strCondition == "" ? strSuitCon : strCondition) + (strOrderByField != "" ? " Order By " + strOrderByField : "");
                    dt = dbcon.GetDataSet(strSql).Tables[0];
                    if (dt.Rows.Count == 0)
                        lRecNo = lRecID;
                    else if (dt.Rows.Count < iRowCount)
                    {
                        if (nOrient == PageOrient.PageDown)
                        {
                            lRecNo = lLastPageBeginRecNo;
                            dr = dr_LastPageBegin;
                        }
                        else
                        {
                            lRecNo = -1;
                            scPageInfo.lBeginRecID = -1;
                            scPageInfo.strPrimaryKeyCon = string.Empty;
                            scPageInfo.strTableSuitCon = "select * from (" + strExceptOrderBySql + ") as t ";
                            return lRecNo;
                        }
                    }
                    else
                    {
                        if (nOrient == PageOrient.PageDown)
                            lRecNo = Convert.ToInt64(dt.Rows[scPageInfo.iRowsPerPage]["RecID"]);
                        else
                            lRecNo = Convert.ToInt64(dt.Rows[scPageInfo.iRowsPerPage - 1]["RecID"]);

                        if (dt.Rows.Count > 0) dr = dt.Rows[scPageInfo.iRowsPerPage - 1];
                    }

                }
                else if (nOrient != PageOrient.None)
                {
                    strSql = "select top " + scPageInfo.iRowsPerPage + " * from (" + strExceptOrderBySql + ") as t " + (strCondition == "" ? strSuitCon : strCondition) + (strOrderByField != "" ? " Order By " + strOrderByField : "");
                    dt = dbcon.GetDataSet(strSql).Tables[0];
                    if (dt.Rows.Count == 0)
                        lRecNo = lRecID;
                    else if (dt.Rows.Count < scPageInfo.iRowsPerPage)
                    {
                        if (nOrient == PageOrient.PageDown)
                            lRecNo = lLastPageBeginRecNo;
                        else if (nOrient == PageOrient.PageUp)
                            lRecNo = -1;
                        else
                            lRecNo = Convert.ToInt64(dt.Rows[0]["RecID"]);
                    }
                    else
                    {
                        if (nOrient == PageOrient.Bottom)
                            lRecNo = lLastPageBeginRecNo;
                        else
                            lRecNo = Convert.ToInt64(dt.Rows[0]["RecID"]);
                    }
                    if (nOrient == PageOrient.Bottom)
                        dr = dr_LastPageBegin;
                    else
                        if (dt.Rows.Count > 0)  dr = dt.Rows[0];
                }
                else
                    lRecNo = Convert.ToInt64(dr["RecID"]);
                #endregion
            }
            else
                lRecNo = -1;

            #region Get TableSuitCon
            if (lRecNo == -1)
            {
                long lBeginRecIDOld = scPageInfo.lBeginRecID;
                scPageInfo.lBeginRecID = lRecNo;
                scPageInfo.strTableSuitCon = GetCondition(OrderByFieldsArray_Org, dr, true, scPageInfo);
                if (scPageInfo.strTableSuitCon.Trim() == string.Empty && (dr == null && (scPageInfo.nOrient == PageOrient.PageDown || scPageInfo.nOrient == PageOrient.PageUp))
                    && lBeginRecIDOld != -1)
                {
                    scPageInfo.lBeginRecID = scPageInfo.lBeginRecID_Last;
                    scPageInfo.strTableSuitCon = scPageInfo.strTableSuitCon_Last;
                }
                return lRecNo;
            }
            else if (lRecNo == lRecID) //First record
            {
                return lRecNo;
            }
            else if ((nOrient == PageOrient.Next || nOrient == PageOrient.PageDown) && lRecID == lLastPageBeginRecNo) //Last record
            {
                return lRecNo;
            }

            strTableSuitCon = GetCondition(OrderByFieldsArray_Org, dr, true, scPageInfo);

            if (strOuterCondition.Trim() != "")
                strTableSuitCon = "( " + strOuterCondition + (strTableSuitCon != "" ? " and " + strTableSuitCon : "") + ")";

            if (strTableSuitCon != "")
                strTableSuitCon = " where " + strTableSuitCon;

            strSuitCon = strTableSuitCon;
            strTableSuitCon = "select * from (" + strExceptOrderBySql + ") as t " + strTableSuitCon;
            scPageInfo.strTableSuitCon = strTableSuitCon;
            scPageInfo.lBeginRecID = lRecNo;
            scPageInfo.strTableSuitCon_Last = scPageInfo.strTableSuitCon;
            scPageInfo.lBeginRecID_Last = scPageInfo.lBeginRecID;
            #endregion

            return lRecNo;
        }
        private static string GetCondition(string[] OrderByFieldsArray, DataRow dr)
        {
            return GetCondition(OrderByFieldsArray, dr, false, new PageInfo());
        }
        private static string GetCondition(string[] OrderByFieldsArray, DataRow dr, bool bTableSuitCon, PageInfo scPageInfo)
        {
            string strValue = string.Empty;
            string strField = string.Empty;
            string strCaseCondition = string.Empty;
            string strCondition = string.Empty;

            if (dr == null) return strCondition;
            if (scPageInfo.strFindField != null && scPageInfo.strFindValue != null && 
                scPageInfo.strFindField.Trim() != string.Empty && scPageInfo.strFindValue.Trim() != string.Empty && !bTableSuitCon)
            {
                strCondition = scPageInfo.strFindField + " like '" + scPageInfo.strFindValue + "%'";
            }
            else
            {
                foreach (string keyf in OrderByFieldsArray)
                {
                    string strSign = "";
                    string f = keyf.Trim().ToUpper();

                    if (f.IndexOf(" ASC", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        strSign = ((bTableSuitCon && (scPageInfo.strFindField != string.Empty || (scPageInfo.nOrient == PageOrient.None || scPageInfo.nOrient == PageOrient.Top || scPageInfo.nOrient == PageOrient.Bottom))) || scPageInfo.nOrient == PageOrient.PageDown ? ">=" : ">"); //strSign = ">="; 
                    }
                    else if (f.IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        strSign = ((bTableSuitCon && (scPageInfo.strFindField != string.Empty || (scPageInfo.nOrient == PageOrient.None || scPageInfo.nOrient == PageOrient.Top || scPageInfo.nOrient == PageOrient.Bottom))) || scPageInfo.nOrient == PageOrient.PageDown ? "<=" : "<"); //strSign = "<=";
                    }
                    else
                        strSign = ((bTableSuitCon && (scPageInfo.strFindField != string.Empty || (scPageInfo.nOrient == PageOrient.None || scPageInfo.nOrient == PageOrient.Top || scPageInfo.nOrient == PageOrient.Bottom))) || scPageInfo.nOrient == PageOrient.PageDown ? ">=" : ">"); //strSign = ">=";


                    strValue = "";
                    //if (f.IndexOf(" ") >= 0) f = f.Substring(0, f.IndexOf(" "));
                    if (f.IndexOf("]") >= 0) f = f.Substring(0, f.IndexOf("]") + 1);
                    f = f.Replace("[", "").Replace("]", "");

                    if (dr.Table.Columns[f] == null) break;
                    string strDateType = dr.Table.Columns[f].DataType.Name;
                    if (strDateType == "String")
                    {
                        strField = f;
                        strValue += dr[f].ToString().Replace("'", "''");
                        strSign += "'";
                        strValue += "'";
                    }
                    else if (strDateType == "DateTime")
                    {
                        strField = "rtrim(convert(char(10), isnull(" + f + "," + "'" + "'" + "),111))";
                        if (IsDate(dr[f].ToString()))
                            strValue = FormatDate(dr[f].ToString(), "yyyy/MM/dd");
                        else
                            strValue += "";
                        strSign += "'";
                        strValue += "'";
                    }
                    else if (strDateType == "Int32" || strDateType == "Decimal")
                    {
                        strField = f;
                        strValue = dr[f].ToString().TrimEnd() == "" ? "null" : dr[f].ToString().TrimEnd();
                    }
                    else if (strDateType == "Bool")
                    {
                        strField = f;
                        strValue = (dr[f].ToString().TrimEnd().ToUpper() == "FALSE" ? "0" : "1");
                    }
                    else
                    {
                        strField = f;
                        strValue = dr[f].ToString().TrimEnd();
                    }
                    if (strCaseCondition != "")
                        strCondition += " or (" + strCaseCondition + " and " + strField + strSign + strValue + ")";
                    else
                        strCondition += "(" + strField + strSign + strValue + ")";

                    strCaseCondition += (strCaseCondition != "" ? " and " : "") + strField + "=" + (strDateType == "String" || strDateType == "DateTime" ? "'" : "") + strValue;
                }
                strCondition = " (" + strCondition + ")";
            }
            return strCondition;
        }

        private static string ReturnSqlAliasName(string strSql, string strRealName)
        {
            string strAliasName = string.Empty;;
            string strFieldRealName = string.Empty;
            string[] strArrRealAndAliasName;

            strArrRealAndAliasName = GetFieldRealAndAliasName(strSql);
            if (strArrRealAndAliasName != null)
            {
                for (int i = 0; i < strArrRealAndAliasName.Length; i++)
                {
                    strFieldRealName = strArrRealAndAliasName[i].Substring(0, strArrRealAndAliasName[i].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase)).ToUpper();
                    if (strFieldRealName.Substring(0, 1) == "[")
                        strFieldRealName = strFieldRealName.Replace("[","").Replace("]","");

                    if (strFieldRealName.IndexOf('.') != -1)
                        strFieldRealName = strFieldRealName.Split('.')[1].ToUpper();

                    if (strFieldRealName.ToUpper() == strRealName.ToUpper())
                    {
                        strAliasName = strArrRealAndAliasName[i].Substring(strArrRealAndAliasName[i].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase) + 4).ToUpper();
                        break;
                    }
                }
            }

            return strAliasName;
        }

        public static string DealWithAlias(string strSql, string InnerSql)
        {
            string[] strArrRealAndAliasName = null;
            string strFieldRealName = string.Empty;
            string strFieldAliasName = string.Empty;
            string strOuterSql = string.Empty;

            strOuterSql = InnerSql;
            strArrRealAndAliasName = ComFunc.GetFieldRealAndAliasName(strSql);
            if (strArrRealAndAliasName != null)
            {
                for (int i = 0; i < strArrRealAndAliasName.Length; i++)
                {
                    strFieldRealName = strArrRealAndAliasName[i].Substring(0, strArrRealAndAliasName[i].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase)).ToUpper();
                    if (strFieldRealName.IndexOf('.') != -1)
                        strFieldRealName = strFieldRealName.Split('.')[1];
                    strFieldAliasName = strArrRealAndAliasName[i].Substring(strArrRealAndAliasName[i].IndexOf(" as ", StringComparison.CurrentCultureIgnoreCase) + 4).ToUpper();
                    if (strFieldRealName.Substring(0, 1) != "[")
                        strFieldRealName = "[" + strFieldRealName + "]";

                    strFieldAliasName = strFieldAliasName.Substring(0, 1) == "[" ? strFieldAliasName : "[" + strFieldAliasName + "]";
                    //InnerSql = InnerSql.ToUpper().Replace(strFieldAliasName, strFieldRealName);
                    strOuterSql = strOuterSql.ToUpper().Replace(strFieldRealName, strFieldAliasName);
                }
            }

            strOuterSql = strOuterSql.ToUpper().Replace("[RECID] ASC,", "").Replace("[RECID] DESC,", "");
            strOuterSql = strOuterSql.TrimEnd().TrimEnd(',');
            return strOuterSql;
        }

        public static string GetTablePrimaryKeyFields(ComFunc dbcon, string strTableName)
        {
            string strKeyFields = "";
            DataView dvw = dbcon.GetDataSet("exec sp_pkeys  @table_name ='" + strTableName + "'").Tables[0].DefaultView;
            foreach (DataRowView drw in dvw)
            {
                strKeyFields += "[" + drw["Column_Name"].ToString().TrimEnd() + "],";
            }

            if (strKeyFields.Length > 1) strKeyFields = strKeyFields.Substring(0, strKeyFields.Length - 1);
            return strKeyFields;
        }

        public static bool IsDate(string Value)
        {
            if (Value == null || Value == string.Empty)
            {
                return false;
            }
            else
            {
                try
                {
                    DateTime.Parse(Value);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }




        public static string FormatDate(string dt, string strFormat)
        {
            string strFormatDate = "";
            if (dt.Trim() == "/  /") return strFormatDate;
            strFormatDate = DateTime.Parse(dt).ToString(strFormat, System.Globalization.DateTimeFormatInfo.InvariantInfo);

            return strFormatDate;
        }

        //public static string FormatDate(DateTime dt)
        //{
        //    string strFormatDate = "";

        //    strFormatDate = dt.ToString(SystemDateOption.optDateFmtMDY, System.Globalization.DateTimeFormatInfo.InvariantInfo);

        //    return strFormatDate;
        //}

        //public static string FormatDate(string dt)
        //{
        //    string strFormatDate = "";

        //    if (dt != "" && dt.Trim() != "/  /")
        //        strFormatDate = DateTime.Parse(dt).ToString(SystemDateOption.optDateFmtMDY, System.Globalization.DateTimeFormatInfo.InvariantInfo);

        //    return strFormatDate;
        //}

        //public static string FormatDate(string dt, DateFormatOption optFormat)
        //{
        //    string strFormatDate = "";
        //    if (dt.Trim() == "/  /" || dt.Trim() == "") return strFormatDate;
        //    strFormatDate = DateTime.Parse(dt).ToString(SystemDateOption.GetFormat(optFormat), System.Globalization.DateTimeFormatInfo.InvariantInfo);

        //    return strFormatDate;
        //}

        public static int Mod(int iX, int iY)
        {

            if (iX == 0 || iY == 0)
                return 0;
            return (iX - (iY * (int)(iX / iY)));
        }

        #region deal with trader prompt
        public static string DealTraderPrompt(ComFunc dbcon, string strName)
        {
            string strTraderPrompt = "";

            if (strName.IndexOf("Salesperson") == -1 && strName.IndexOf("Trader") == -1 &&
                strName.IndexOf("Slp#") == -1 && strName.IndexOf("Trd#") == -1) { return strName; }

            strTraderPrompt = dbcon.GetValue("select SwitchValue from mMiscSwitch where SwitchName = 'TraderPrompt' and SystemName ='M'").Trim();
            if (strTraderPrompt == string.Empty) return strName;

            if (strName.IndexOf("Salesperson") != -1 || strName.IndexOf("Trader") != -1)
            {

                if (strName.IndexOf("Salesperson") != -1)
                {
                    strName = strName.Replace("Salesperson", strTraderPrompt);
                }
                else if (strName.IndexOf("Trader") != -1)
                {
                    strName = strName.Replace("Trader", strTraderPrompt);
                }
            }
            else if (strName.IndexOf("Slp#") != -1 || strName.IndexOf("Trd#") != -1)
            {

                if (strTraderPrompt == "Salesperson")
                    strTraderPrompt = "Slp#";
                else
                    strTraderPrompt = "Trd#";

                if (strName.IndexOf("Slp#") != -1)
                {
                    strName = strName.Replace("Slp#", strTraderPrompt);
                }
                else if (strName.IndexOf("Trd#") != -1)
                {
                    strName = strName.Replace("Trd#", strTraderPrompt);
                }
            }
            return strName;
        }
        #endregion

        #region CvtFunc same as CommonClasses.cs

        #region GetFieldType: Get Field Type According to table name and field name
        /// <summary>
        /// Get Field Type According to table name and field name
        /// </summary>
        /// <param name="dBAccess"></param>
        /// <param name="strTableName"></param>
        /// <param name="strFieldName"></param>
        /// <returns></returns>
        public static DataType GetFieldType(ComFunc dBAccess, string strTableName, string strFieldName)
        {
            string strSql = "SELECT TOP 0 {0} FROM {1}";
            ComFunc  dbAccess;
            DataSet DS = new DataSet();
            
            if (dBAccess == null)
                dbAccess = new ComFunc();
            else
                dbAccess = dBAccess;

            strSql = string.Format(strSql, strFieldName, strTableName);
            DS = dbAccess.GetDataSet(strSql);

            return ConvertSqlDataType(DS.Tables[0].Columns[0].DataType.Name);
        }
        #endregion

        #region getFieldString : get fields string from SQL
        /// <summary>
        /// get fields string from SQL
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static string GetFieldsStringFromSQL(string strSql)
        {

            int i, j;
            i = strSql.ToUpper().IndexOf("SELECT", 0) + "SELECT".Length;
            j = strSql.ToUpper().IndexOf(" FROM ", 0);
            if (i >= 0 && j >= 0)
                return strSql.Substring(i, j - "SELECT".Length);
            else
                return "";


        }
        #endregion

        #region ConvertSqlDataTypeToWPDataType
        public static DataType ConvertSqlDataType(string strDataTypeName)
        {
            DataType dataType = DataType.String;

            if (strDataTypeName == "Boolean")
            {
                dataType = DataType.Bool;
            }
            else if (strDataTypeName == "Int16" || strDataTypeName == "Int32" || strDataTypeName == "Int64" || strDataTypeName == "UInt16" || strDataTypeName == "UInt32" || strDataTypeName == "UInt64")
            {
                dataType = DataType.Integer;
            }
            else if (strDataTypeName == "Decimal" || strDataTypeName == "Single" || strDataTypeName == "Double")
            {
                dataType = DataType.Numeric;
            }
            else if (strDataTypeName == "DateTime")
            {
                dataType = DataType.DateTime;
            }
            else if (strDataTypeName == "String" || strDataTypeName == "Char" || strDataTypeName == "Varchar")
            {
                dataType = DataType.String;
            }
            else if (strDataTypeName == "Byte[]" || strDataTypeName == "Byte" || strDataTypeName == "TimeSpan" || strDataTypeName == "SByte")
            {
                dataType = DataType.Byte;
            }

            return dataType;
        }
        #endregion

        public static string ToString(object obj)
        {
            if (obj == null) return "";

            return obj.ToString().TrimEnd();
        }


        public static string ToLeftString(object obj, int iLength)
        {
            if (obj == null) return "";
            string strTo = obj.ToString().TrimEnd();
            if (strTo.Length > iLength)
                return strTo.Substring(0, iLength);
            else
                return strTo;
        }
        public static string ToRightString(object obj, int iLength)
        {
            if (obj == null) return "";
            string strTo = obj.ToString().TrimEnd();
            if (strTo.Length > iLength)
                return strTo.Substring(strTo.Length - iLength);
            else
                return strTo;
        }
        public static string ToSubstring(object obj, int iStartIndex, int iLength)
        {
            if (obj == null) return "";
            string strTo = obj.ToString().TrimEnd();
            if (iStartIndex > strTo.Length || iStartIndex < 0 || iLength < 0)
            {
                return "";
            }
            else if (iStartIndex + iLength > strTo.Length)
            {
                return strTo.Substring(iStartIndex); ;
            }
            else
                return strTo.Substring(iStartIndex, iLength);
        }


        public static string ToDateString(object obj)
        {
            if (obj == null || obj.ToString().TrimEnd() == "") return "";

            return FormatDate(obj.ToString(),"MM/dd/yyyy");
        }

        public static DateTime ToDateTime(object obj)
        {
            if (obj == null || obj.ToString().TrimEnd() == "" || obj.ToString().Trim() == "/  /") return new DateTime(1900, 1, 1);

            return DateTime.Parse(obj.ToString());
        }

        public static double ToDouble(object obj)
        {
            return ToDouble(obj, -1);
        }
        public static double ToDouble(object obj, int intDecimalPoint)
        {
            string strFormatStyle = "";

            if (obj == null || obj.ToString().TrimEnd() == "") return 0;

            if (intDecimalPoint >= 0)
            {
                strFormatStyle = "N." + intDecimalPoint.ToString();
                return double.Parse(FormatNumeric(obj.ToString(), strFormatStyle));
            }
            else
            {
                //return double.Parse(obj.ToString());

                return double.Parse(FormatNumeric(obj.ToString(), "n.n"));

            }
        }

        public static int ToInt(object obj)  //Int32
        {
            if (obj == null || obj.ToString().TrimEnd() == "" || !IsNumeric(obj.ToString())) return 0;

            return int.Parse(FormatNumeric(obj.ToString(), "n.0"));
        }

        public static long ToLong(object obj) //Int64
        {
            if (obj == null || obj.ToString().TrimEnd() == "") return 0;

            return long.Parse(obj.ToString());
        }

        public static bool ToBoolean(object obj)
        {
            if (obj == null || obj.ToString().TrimEnd() == "") return false;

            if (obj.ToString().ToUpper() == "YES") return true;
            if (obj.ToString().ToUpper() == "NO") return false;

            return Convert.ToBoolean(obj.ToString());
        }
        #endregion

    }
}
